class JirigoTaskDashboard(object):
def __init__(self, data={}):
print("Initializing JirigoTaskDashboard")
print(f'In for Create Dashboard **** :{data}')
# self.task_no = data.get('task_no')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_task_summaries(self):
response_data = {}
self.logger.debug("Inside get_task_audit")
query_sql = """
WITH t AS
(SELECT 'issueStatus' col_header,
issue_status col_ref_name,
count(*) cnt
FROM ttasks t
GROUP BY issue_status
UNION ALL SELECT 'issueType' col_header,
t.issue_type,
count(*) cnt
FROM
(SELECT CASE issue_type
WHEN 'Bug' THEN 'Bug'
ELSE 'Others'
END AS issue_type
FROM ttasks t2) AS t
GROUP BY issue_type
UNION ALL SELECT 'Severity' col_header,
severity,
count(*) cnt
FROM ttasks t
WHERE SEVERITY IN ('High',
'Critical')
GROUP BY severity)
SELECT json_object_agg(col_header||col_ref_name, cnt)
FROM t ;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_task_audit Success with {row_count} row(s) Task ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While getting Task Audit {error}')
raise
class JirigoTicketAudit(object):
def __init__(self,data={}):
print("Initializing JirigoTicketAudit")
print(f'In for Create TicketAudit **** :{data}')
self.ticket_no = data.get('ticket_no')
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_ticket_audit(self):
response_data={}
self.logger.debug("Inside get_ticket_audit")
query_sql="""
WITH t AS (
SELECT ticket_no,
column_name,
display_column_name,
CASE column_name
WHEN 'estimated_time'
THEN (round(old_value::NUMERIC(5)/60,2))::text
ELSE old_value::text
END as old_value,
CASE column_name
WHEN 'estimated_time'
THEN (round(new_value::NUMERIC(5)/60,2))::text
ELSE new_value::text
END as new_value,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date
FROM htickets
where ticket_no=%s
order by hticket_int_id desc limit 15
)
SELECT json_agg(t) from t;
"""
values=(self.ticket_no,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select get_ticket_audit Success with {row_count} row(s) Ticket ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While getting Ticket Audit {error}')
raise
class JirigoGlobalSearch(object):
def __init__(self, data={}):
print("Initializing JirigoGlobalSearch")
print(f'In for JirigoGlobalSearch **** :{data}')
self.search_text = data.get('search_text')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.jdb = JirigoDBConn()
self.logger = Logger()
def global_search(self):
response_data = {}
self.logger.debug(" Inside global_search")
query_sql = """
WITH tc AS (SELECT item_no,summary,item_type
FROM v_all_tickets_tasks vatt
WHERE to_tsvector(item_no||'-'||summary||description) @@ plainto_tsquery (%s)
LIMIT 10
)
select json_agg(tc) from tc;
"""
values = (self.search_text, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) global_search {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While global_search {error}')
raise
class JirigoDataExtract(object):
def __init__(self, data={}):
print("Initializing JirigoMenus")
self.project_id = data.get('project_id', '')
self.start_date = data.get('start_date', '')
self.end_date = data.get('end_date', '')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_all_tickets_for_time_range(self):
response_data = {}
self.logger.debug("Inside get_all_tickets_for_time_range")
query_sql = """
WITH t AS (
SELECT
tt.ticket_no,tt.summary,tt.description,tt.issue_status,
tt.issue_type,tt.severity,tt.priority,tt.environment,tt.is_blocking,
get_user_name(tt.created_by) creeated_by,tt.created_date,
get_user_name(tt.modified_by) modified_by,tt.modified_date,
get_user_name(tt.reported_by) reported_by,tt.reported_date,
get_proj_name(tt.project_id) project_name,
get_user_name(tt.assignee_id) assigned_to,tt.module,tt.channel,
tta.activity,tta.time_spent time_spent_mins,
tta.actual_date,get_user_name(tta.time_spent_by) time_logged_by,
tta.other_activity_comment,tta.timelog_comment
FROM ttickets tt
LEFT OUTER JOIN tticket_actuals tta
ON tt.ticket_no =tta.ticket_no
WHERE tt.created_date >= %s
AND tt.created_date <= %s
)
SELECT json_agg(t) from t
"""
values = (
self.start_date,
self.end_date,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_tickets_for_time_range {error}')
raise
def get_all_tasks_for_time_range(self):
response_data = {}
self.logger.debug("Inside get_all_tasks_for_time_range")
query_sql = """
WITH t AS (
SELECT
tt.task_no,tt.summary,tt.description,tt.issue_status,
tt.issue_type,tt.severity,tt.priority,tt.environment,tt.is_blocking,
get_user_name(tt.created_by) creeated_by,tt.created_date,
get_user_name(tt.modified_by) modified_by,tt.modified_date,
get_user_name(tt.reported_by) reported_by,tt.reported_date,
get_proj_name(tt.project_id) project_name,
get_user_name(tt.assignee_id) assigned_to,tt.module_name,
tta.activity,tta.actual_time_spent time_spent_mins,
tta.actual_date,get_user_name(tta.time_spent_by) time_logged_by,
tta.other_activity_comment,tta.timelog_comment
FROM ttasks tt
LEFT OUTER JOIN ttask_actuals tta
ON tt.task_no =tta.task_no
WHERE tt.created_date >= %s
AND tt.created_date <= %s
)
SELECT json_agg(t) from t
"""
values = (
self.start_date,
self.end_date,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_tasks_for_time_range {error}')
raise
class JirigoUsers(object):
def __init__(self, data):
print("Initializing JirigoUsers")
self.user_id = data.get('user_id', 0)
self.first_name = data.get('first_name', '')
self.last_name = data.get('last_name', '')
self.email = data.get('email', '')
self.is_active = data.get('is_active', '')
self.salt = data.get('salt', '')
self.password = data.get('password', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', 0)
self.modified_date = datetime.datetime.now()
self.assigned_projects = data.get('assigned_projects', [])
self.role_id = data.get('role_id', '')
self.project_id = data.get('project_id', '')
self.current_route = data.get('current_route', '')
self.jdb = JirigoDBConn()
self.logger = Logger()
@classmethod
def for_create_update_users(cls, data):
print("-" * 40)
print(f'In for_create_update_users :{data}')
print("-" * 40)
return cls(data)
def register_user(self):
response_data = {}
print("Inside Create User")
self.salt = JirigoUsers.generate_salt()
insert_sql = """ INSERT INTO TUSERS(first_name,last_name,email,salt,password,
created_by,created_date,is_active)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s) returning user_id;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.salt,
JirigoUsers.get_password_digest(self.password, self.salt),
1,
datetime.datetime.today(),
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
user_id = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
print(
f'User Creation Success with {row_count} row(s) User ID {user_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"userId": user_id, "rowCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating User {error}')
raise
def create_user(self):
response_data = {}
print("Inside Create User")
self.salt = JirigoUsers.generate_salt()
insert_sql = """ INSERT INTO TUSERS(first_name,last_name,email,salt,password,
created_by,created_date,is_active)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s) returning user_id;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.salt,
JirigoUsers.get_password_digest(self.password, self.salt),
1,
datetime.datetime.today(),
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
add_user_projects_sql = """
INSERT INTO TUSER_PROJECTS
(user_id,project_id,created_date,created_by,is_active,default_project)
VALUES (%s,%s,%s,%s,%s,%s);
"""
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
user_id = cursor.fetchone()[0]
print(f'self.assigned_projects {self.assigned_projects}')
print(f'User id is {user_id}')
default_project = 'Y'
for i in range(len(self.assigned_projects)):
print(f'Inserting project id {self.assigned_projects[i]}')
values = (
user_id,
self.assigned_projects[i],
self.created_date,
self.created_by,
'Y',
default_project,
)
print(
f'TUSER_PROJECTS Insert : {add_user_projects_sql} {values}'
)
cursor.execute(add_user_projects_sql, values)
default_project = 'N'
self.jdb.dbConn.commit()
row_count = cursor.rowcount
print(
f'User Creation Success with {row_count} row(s) User ID {user_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"userId": user_id, "rowCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating User {error}')
raise
def get_all_users(self):
response_data = {}
self.logger.debug("Inside get_all_users")
query_sql = """
WITH t AS (
select first_name,last_name,user_id,get_user_name(user_id) as name,email,
is_active,password_change_date
from tusers
order by first_name,last_name
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) data {json_data}')
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select Users {error}')
raise
def get_user_details(self):
response_data = {}
self.logger.debug("Inside get_all_users")
query_sql = """
WITH t AS (
select user_id,first_name||'.'||last_name as name,email
from tusers
where is_active='Y'
and user_id=%s
)
SELECT json_agg(t) from t;
"""
values = (self.user_id, )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_details Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_user_details {error}')
raise
def get_user_names(self, user_name):
response_data = {}
self.logger.debug("Inside get_user_names")
query_sql = """
WITH t AS (
select user_id,get_user_name(user_id) as name,email
from tusers
where is_active='Y'
and lower(first_name||'.'||last_name) like lower(%s)
)
SELECT json_agg(t) from t;
"""
user_name = str(user_name).replace('%', '####')
values = (f'{user_name}%', )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_names Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_user_names {error}')
raise
def update_user_details(self):
response_data = {}
self.logger.debug("Inside update_user_details")
update_sql = """
UPDATE TUSERS
SET first_name=%s,
last_name=%s,
email=%s,
modified_by=%s,
modified_date=%s
WHERE user_id=%s;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.modified_by,
datetime.datetime.today(),
self.user_id,
)
self.logger.debug(f'update_user_details : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Ticket {error}')
raise
def validate_userid_password(self):
response_data = {}
self.logger.debug("Inside validate_userid_password")
try:
salt = self.get_user_salt()
except Exception as error:
print("Error while retrieving user Salt")
response_data['dbQryStatus'] = 'Failure'
response_data['dbQryResponse'] = 'Invalid User Id or Password'
return response_data
print(f'User Salt is {salt}')
query_sql = """
WITH t AS (
SELECT tu.user_id,get_user_name(tu.user_id) as user_name,
tu.email,tu.is_active,coalesce(tup.project_id,0) project_id,
get_proj_name(tup.project_id) project_name,
get_role_id_for_user(tu.user_id,tup.project_id) role_id,
get_role_name(get_role_id_for_user(tu.user_id,tup.project_id)) role_name
FROM tusers tu
LEFT OUTER JOIN tuser_projects tup
ON tu.user_id = tup.user_id
WHERE tu.email=%s
AND tu.password=%s
AND COALESCE (tup.default_project,'Y') ='Y'
)
SELECT json_agg(t) from t;
"""
values = (
self.email,
JirigoUsers.get_password_digest(self.password, salt),
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select validate_password Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'Failure'
response_data['dbQryResponse'] = 'Invalid User Id or Password'
else:
self.logger.debug(f'json_data[0] {json_data[0]}')
if (json_data[0]['is_active'] != 'Y'):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Inactive User, Contact Administrator.'
elif (json_data[0]['project_id'] == 0):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Active User, but no Project Assigned. Contact Adminstrator.'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select validate_password {error}')
raise
def set_user_password(self):
response_data = {}
self.logger.debug("Inside set_password")
update_sql = """
UPDATE TUSERS
SET password=%s,
salt=%s,
modified_by=%s,
modified_date=%s,
password_change_date=%s
WHERE (user_id=%s
OR email=%s)
"""
salt = JirigoUsers.generate_salt()
values = (
JirigoUsers.get_password_digest(self.password, salt),
salt,
self.modified_by,
datetime.datetime.now(),
datetime.datetime.now(),
self.user_id,
self.email,
)
self.logger.debug(f'set_password : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While set_user_password{error}')
raise
def get_user_salt(self):
self.logger.debug("Inside get_user_names")
query_sql = """
select salt
from tusers
WHERE ( email=%s
OR user_id=%s )
"""
values = (
self.email,
self.user_id,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_salt Success with {row_count} row(s) data {data}'
)
return data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select get_user_salt {error}')
raise
def authenticate_route_for_user(self):
response_data = {}
self.logger.debug("Inside authenticate_route_for_user")
query_sql = """
WITH t AS (
SELECT *
FROM v_user_projects_role_menu vuprm
WHERE vuprm.project_id =%s
AND user_id=%s
AND role_id=%s
AND (path = %s )
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.user_id,
self.role_id,
self.current_route,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
print("=^^=" * 30)
print(json_data)
print("=^^=" * 30)
row_count = cursor.rowcount
self.logger.debug(
f'Select authenticate_route_for_user Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Access Denied To Current Route'
else:
self.logger.debug(f'json_data[0] {json_data[0]}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = 'Access Granted'
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While Select authenticate_route_for_user {error}')
raise
def get_all_valid_routes_for_user(self):
response_data = {}
self.logger.debug("Inside get_all_valid_routes_for_user")
query_sql = """
WITH t AS (
SELECT *
FROM v_user_projects_role_menu vuprm
WHERE vuprm.project_id =%s
AND user_id=%s
AND role_id=%s
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.user_id,
self.role_id,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_valid_routes_for_user {error}')
raise
def update_toggle_active_status(self):
response_data = {}
self.logger.debug("Inside update_toggle_active_status")
update_sql = """
UPDATE TUSERS
SET is_active=%s,
modified_by=%s,
modified_date=%s
WHERE user_id=%s;
"""
values = (
self.is_active,
self.modified_by,
self.modified_date,
self.user_id,
)
self.logger.debug(
f'update_toggle_active_status : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating User {error}')
raise
@staticmethod
def get_password_digest(p_password, salt, iterations=1000000, dklen=64):
# print(f'{p_password} {p_password.encode()} {bytes(p_password,"utf-8")}')
return pbkdf2_hmac('sha512', p_password.encode(), salt.encode(),
iterations, dklen)
@staticmethod
def generate_salt(salt_len=20):
return ''.join([
random.choice(string.ascii_letters + string.digits +
string.punctuation) for _ in range(salt_len)
])
class JirigoTodos(object):
def __init__(self,data={}):
print("Initializing JirigoTodos")
print(f'In for Create/Update/Delete todos ****')
pprint(data)
self.project_id = data.get('project_id')
self.user_id = data.get('user_id')
self.todo_id = data.get('todo_id')
self.label_id = data.get('label_id')
self.todo_label = data.get('todo_label')
self.end_date = datetime.datetime.now() if data.get('end_date') == None else data.get('end_date')
self.todo_text = data.get('todo_text')
self.todo_status= data.get('todo_status')
self.category_id= data.get('category_id')
self.category= data.get('category')
self.category_color= data.get('category_color')
self.created_by= data.get('created_by')
self.modified_by= data.get('modified_by')
self.interval_days=data.get('interval_days')
self.created_date=datetime.datetime.now()
self.modified_date=datetime.datetime.now()
# self.limit=data.get('limit',5)
# self.offset=data.get('offset',0)
self.limit=10000000
self.offset=0
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_all_todos_for_user(self):
response_data={}
self.logger.debug("Inside get_all_todos_for_user")
query_sql="""
with t as (
SELECT *
FROM v_all_todos_with_labels
WHERE created_by=%s
AND rnm=1
ORDER BY end_date
LIMIT %s
OFFSET %s
)
select json_agg(t) from t ;
"""
self.logger.debug(f'Select all todos : {query_sql}')
values=(self.user_id,self.limit,self.offset,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_todos_for_user Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data if json_data != None else 'No Rows Fetched'
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_todos_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_todos_for_user {error}')
raise
def get_all_todos_for_user_filtered_by_label(self):
response_data={}
self.logger.debug("Inside get_all_todos_for_user_filtered_by_label")
query_sql="""
with t as (
SELECT *
FROM v_all_todos_with_labels
WHERE created_by=%s
AND label_id=%s
ORDER BY end_date
LIMIT %s
OFFSET %s
)
select json_agg(t) from t ;
"""
self.logger.debug(f'Select all todos : {query_sql}')
values=(self.user_id,self.label_id,self.limit,self.offset,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_todos_for_user_filtered_by_label Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data if json_data != None else 'No Rows Fetched'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_todos_for_user_filtered_by_label {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_todos_for_user_filtered_by_label {error}')
raise
def get_all_todos_for_user_filtered_by_status(self):
response_data={}
self.logger.debug("Inside get_all_todos_for_user_filtered_by_status")
query_sql="""
with t as (
SELECT *
FROM v_all_todos_with_labels
WHERE created_by=%s
AND todo_status=%s
ORDER BY end_date
LIMIT %s
OFFSET %s
)
select json_agg(t) from t ;
"""
self.logger.debug(f'Select all todos : {query_sql}')
values=(self.user_id,self.todo_status,self.limit,self.offset,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_todos_for_user_filtered_by_status Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data if json_data != None else 'No Rows Fetched'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_todos_for_user_filtered_by_status {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_todos_for_user_filtered_by_status {error}')
raise
def get_todos_for_user_by_interval(self):
response_data={}
self.logger.debug("Inside get_todos_for_user_by_interval")
query_sql="""
with t as (
SELECT
td.*,ttc.*
FROM
ttodos td
LEFT OUTER JOIN ttodo_categories ttc
ON td.category_id=ttc.category_id
WHERE
td.created_by =%s
AND DATE(end_date) between DATE(now()) AND DATE(now()) + INTERVAL %s
ORDER BY
td.created_date DESC,
td.modified_date DESC
LIMIT %s
OFFSET %s
)
select json_agg(t) from t ;
"""
self.interval_days= f'{self.interval_days} days' if int(self.interval_days) > 1 else f'{self.interval_days} day'
values=(self.user_id,self.interval_days,self.limit,self.offset,)
self.logger.debug(f'Select all todos : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_todos_for_user_by_interval Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_todos_for_user_by_interval {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_todos_for_user_by_interval {error}')
raise
def get_todo_categories_for_user(self):
response_data={}
self.logger.debug("Inside get_todo_categories_for_user")
query_sql="""
with t as (
SELECT *
FROM ttodo_categories
WHERE user_id=%s
ORDER BY category
)
select json_agg(t) from t ;
"""
values=(self.user_id,)
self.logger.debug(f'Select all todos : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_todo_categories_for_user Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_todo_categories_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_todo_categories_for_user {error}')
raise
def create_todo_for_user(self):
response_data={}
self.logger.debug("Inside create_todo_for_user")
query_sql="""
INSERT INTO
ttodos (todo_text,todo_status,category_id,end_date,created_by,created_date)
VALUES (%s,%s,%s,%s,%s,%s)
RETURNING todo_id
"""
self.logger.debug(f'Select all todos : {query_sql}')
values=(self.todo_text,self.todo_status,self.category_id,self.end_date,self.created_by,self.created_date,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
self.jdb.dbConn.commit()
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'create_todo_for_user Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Todo Created '+str(json_data)
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While create_todo_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While create_todo_for_user {error}')
raise
def create_category_for_user(self):
response_data={}
self.logger.debug("Inside create_category_for_user")
query_sql="""
INSERT INTO
ttodo_categories (category,user_id,colorhex)
VALUES (%s,%s)
RETURNING category_id
"""
self.logger.debug(f'Create Category : {query_sql}')
values=(self.category,self.user_id,self.category_color,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
self.jdb.dbConn.commit()
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'create_category_for_user Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Category Created '+str(json_data)
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While create_category_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While create_category_for_user {error}')
raise
def del_todo_for_user(self):
response_data={}
self.logger.debug("Inside del_todo_for_user")
query_sql="""
DELETE FROM ttodos WHERE todo_id=%s
"""
values=(self.todo_id,)
self.logger.debug(f'Select all todos : {query_sql} {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'del_todo_for_user Select Success with {row_count} row(s) data')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=f'{row_count} Record Deleted'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While del_todo_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While del_todo_for_user {error}')
raise
def upd_todo_for_user(self):
response_data={}
self.logger.debug("Inside upd_todo_for_user")
query_sql="""
UPDATE ttodos
SET todo_text=%s,
end_date=%s,
todo_status=%s,
category_id=%s,
modified_by=%s,
modified_date=%s
WHERE todo_id=%s
"""
values=(self.todo_text,self.end_date,self.todo_status,self.category_id,self.modified_by,self.modified_date,self.todo_id,)
self.logger.debug(f'Select all todos : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
self.jdb.dbConn.commit()
row_count=cursor.rowcount
self.logger.debug(f'upd_todo_for_user Success with {row_count} row(s) data')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=f'{row_count} Record Updated'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference upd_todo_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference upd_todo_for_user {error}')
raise
def upd_category_for_user(self):
response_data={}
self.logger.debug("Inside upd_category_for_user")
query_sql="""
UPDATE ttodo_categories
SET category=%s,
colorhex=%s
WHERE category_id=%s
"""
self.logger.debug(f'Select all todos : {query_sql}')
values=(self.category,self.category_color,self.category_id,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'upd_category_for_user Select Success with {row_count} row(s) data')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Record Updated'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference upd_category_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference upd_category_for_user {error}')
raise
def create_label_for_todo(self):
response_data={}
row_count=0
self.logger.debug("Inside create_label_for_todo")
label_sql="""
INSERT INTO
ttodo_labels (label)
VALUES (%s)
RETURNING label_id
"""
label_todo_link_sql="""
INSERT INTO
ttodo_label_link (label_id,todo_id)
VALUES (%s,%s)
"""
self.logger.debug(f'Create Label : {label_sql}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
#
if self.label_id == "":
values=(self.todo_label,)
cursor.execute(label_sql,values)
self.jdb.dbConn.commit()
label_id=cursor.fetchone()[0]
row_count=cursor.rowcount
else :
label_id=self.label_id
values=(label_id,self.todo_id,)
print(values)
cursor.execute(label_todo_link_sql,values)
self.jdb.dbConn.commit()
self.logger.debug(f'create_label_for_todo Success with {row_count} row(s) data {label_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Label Created with id :'+str(label_id)
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While create_label_for_todo {error}')
if(self.jdb.dbConn):
print(f'Error While create_label_for_todo {error}')
raise
def get_todo_labels_for_user(self):
response_data={}
self.logger.debug("Inside get_todo_labels_for_user")
query_sql="""
with t as (
SELECT
distinct
tl.label,tl.label_id
FROM
ttodo_label_link tll ,
ttodo_labels tl,
ttodos t
WHERE
tll.label_id = tl.label_id
AND t.todo_id = tll.todo_id
AND t.created_by = %s
)
select json_agg(t) from t ;
"""
values=(self.user_id,)
self.logger.debug(f'Select all todos : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_todo_labels_for_user Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_todo_labels_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_todo_labels_for_user {error}')
raise
def del_label_for_todo(self):
response_data={}
self.logger.debug("Inside create_label_for_todo")
label_sql="""
CALL del_todo_label(%s,%s);
"""
self.logger.debug(f'Delete Label : {label_sql}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
values=(self.label_id,self.todo_id,)
cursor.execute(label_sql,values)
self.jdb.dbConn.commit()
self.logger.debug(f'create_label_for_todo Success with row(s) data {self.label_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Label Deleted'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While create_label_for_todo {error}')
if(self.jdb.dbConn):
print(f'Error While create_label_for_todo {error}')
raise
class JirigoBoardWorkflow(object):
def __init__(self, data={}):
print("Initializing JirigoWorkflow")
print(f'In for Create/Update/Delete Workflow ****')
pprint(data)
self.project_id = data.get('project_id')
self.workflow_id = data.get('workflow_id')
self.workflow_name = data.get('workflow_name')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_workflow_steps(self):
response_data = {}
self.logger.debug("Inside get_workflow_steps")
query_sql = """
with t as (
select step_id,step_name,workflow_id
from tboard_workflow_steps tws
where tws.workflow_id=%s
)
select json_build_object('rowData',json_agg(t.refs)) from t ;
"""
self.logger.debug(f'Select all sprint : {query_sql}')
values = (self.workflow_id, )
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
print(json_data)
row_count = cursor.rowcount
self.logger.debug(
f'get_workflow_steps Select Success with {row_count} row(s) data {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data['rowData']
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_workflow_steps {error}')
if (self.jdb.dbConn):
print(
f'Error While Select Reference get_workflow_steps {error}')
raise
def get_all_workflows(self):
response_data = {}
self.logger.debug("Inside get_all_workflows")
query_sql = """
with t as (
SELECT workflow_id,workflow_name,workflow_type,
from tboard_workflow_master
)
select json_build_object('rowData',json_agg(t.refs)) from t ;
"""
self.logger.debug(f'Select all sprint : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
print(json_data)
row_count = cursor.rowcount
self.logger.debug(
f'get_all_workflows Select Success with {row_count} row(s) data {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data['rowData']
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_workflows {error}')
if (self.jdb.dbConn):
print(
f'Error While Select Reference get_workflow_steps {error}')
raise
class JirigoProjects(object):
def __init__(self, data):
print("Initializing JirigoProject")
pprint(data)
self.user_id = data.get('user_id', 0)
self.project_id = data.get('project_id', 0)
self.project_name = data.get('project_name')
self.parent_project_id = data.get('parent_project_id', 0)
self.project_abbr = data.get('project_abbr')
self.project_type = data.get('project_type')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = None
self.modified_date = None
self.is_active = data.get('is_active', 'Y')
self.jdb = JirigoDBConn()
self.logger = Logger()
def create_project(self):
response_data = {}
self.logger.debug("Inside Create Project")
insert_sql = """ INSERT INTO TPROJECTS(project_name,project_abbr,project_type,created_by,created_date,is_active,workflow_id)
VALUES (%s,%s,%s,%s,%s,%s,1) returning project_id;
"""
values = (
self.project_name,
self.project_abbr,
self.project_type,
self.created_by,
self.created_date,
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
insert_project_refs = """
INSERT INTO tref_master (ref_category ,ref_name ,ref_value ,is_active ,
created_by ,created_date ,order_id,project_id )
SELECT ref_category ,ref_name ,ref_value ,is_active ,
created_by ,%s ,order_id,%s
FROM tref_master
WHERE project_id=%s;
"""
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
project_id = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) New Project ID {project_id}'
)
values = (
self.created_date,
project_id,
self.parent_project_id,
)
cursor.execute(insert_project_refs, values)
# cursor.fetchone()[0]
row_count = cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(
f'Insert References Success with {row_count} row(s) ')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"projectId": project_id,
"rowCount": 1
}
print("Going to sleep for 10 seconds")
time.sleep(10)
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Project {error}')
self.logger.debug(f'Error While Creating Project {error}')
raise
def get_all_projects(self):
response_data = {}
self.logger.debug("Inside get_all_projects")
query_sql = """
WITH t AS (
select *
from tprojects
where is_active='Y'
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_all_projects Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select Projects {error}')
raise
def get_all_projects_for_user(self):
response_data = {}
self.logger.debug("Inside get_all_projects_for_user")
query_sql = """
WITH t AS (
SELECT t.user_id,t.email,tup.default_project ,
tp.project_name ,tp.project_id,tp.project_abbr,
get_user_name(t.user_id) user_name
FROM tuser_projects tup,tusers t ,tprojects tp
WHERE tup.project_id = tp.project_id
AND tup.user_id =t.user_id
AND t.user_id =%s
ORDER BY tp.project_id
)
SELECT json_agg(t) from t;
"""
values = (self.user_id, )
self.logger.debug(f'Select : {query_sql} values{values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_all_projects_for_user Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_all_projects_for_user {error}')
raise
def get_project_details(self):
response_data = {}
self.logger.debug("Inside get_project_details")
query_sql = """
WITH t AS (
SELECT tp.*
FROM tprojects tp
WHERE tp.project_id=%s
)
SELECT json_agg(t) from t;
"""
values = (self.project_id, )
self.logger.debug(f'Select : {query_sql} values{values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_project_details Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_all_projects_for_user {error}')
raise
class JirigoScrumBoard(object):
def __init__(self, data={}):
print("Initializing JirigoScrumBoard")
print(f'In for Create Sprints **** :{data}')
# self.sprint_name=data.get('sprint_name','')
# self.project_name=data.get('project_name','')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
# self.sprint_tasks=data.get('sprint_tasks')
# self.sprint_status=data.get('sprint_status')
self.sprint_id = data.get('sprint_id')
self.sprint_tasks = data.get('sprint_tasks')
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
# self.start_date=data.get('start_date')
# self.end_date=data.get('end_date')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_all_tasks_of_sprint_for_scrum_board(self):
response_data = {}
self.logger.debug("Sprints Inside get_all_not_closed_tickets")
query_sql = """
WITH t AS (
SELECT
trm.ref_value ,trm.ref_key,tbws.step_id,
tsptt.*
FROM
tboard_workflow_master twm
INNER JOIN tboard_workflow_steps tbws ON
twm.workflow_id = tbws.workflow_id
INNER JOIN tref_master trm ON
trm.ref_key = tbws.step_key
LEFT OUTER JOIN (
SELECT
t.task_int_id, t.task_no, t.summary, t.issue_status,
t.issue_type, t.severity, t.priority, t.module_name,
ts.sprint_name, trm.ref_value as ref_value1,
get_sumof_task_actuals(t.task_no) task_actuals,
get_task_estimate(t.task_no) task_estimate,
get_task_completed_percent(t.task_no) task_completed_pct,
t.start_date,t.end_date
FROM
ttasks t, tsprint_tasks tt, tsprints ts,
tboard_workflow_master tbwm,
tboard_workflow_steps tbws,
tref_master trm
WHERE
t.task_no = tt.task_no
AND tt.sprint_id = %s
AND tt.sprint_id = ts.sprint_id
AND ts.workflow_id = tbwm.workflow_id
AND tbwm.workflow_id = tbws.workflow_id
AND tbws.step_key = trm.ref_key
AND tt.board_step = tbws.step_key ) AS tsptt ON
trm.ref_value = tsptt.ref_value1
WHERE
tbws.workflow_id = (SELECT workflow_id
FROM tsprints
WHERE sprint_id=%s)
)
SELECT
JSON_AGG(col)
FROM
(
SELECT
JSON_BUILD_OBJECT(
t.step_id||'$'||t.ref_value,
JSON_AGG(
JSON_BUILD_OBJECT(
'task_no', t.task_no, 'summary', t.summary,
'issue_status', t.issue_status,
'issue_type', t.issue_type, 'severity',
t.severity, 'priority', t.priority,
'module_name', t.module_name, 'sprint_name',
t.sprint_name,'step_id',t.step_id,
'ref_key',t.ref_key,'task_completed_pct',task_completed_pct,
'task_actuals',task_actuals,'task_estimate',task_estimate,
'start_date',start_date,'end_date',end_date
)
)
) AS col
FROM
t
GROUP BY
t.step_id||'$'||t.ref_value
ORDER BY t.step_id||'$'||t.ref_value
) c;
"""
values = (
self.sprint_id,
self.sprint_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) get_all_tasks_of_sprint_for_scrum_board ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_all_tasks_of_sprint_for_scrum_board {error}'
)
raise
def update_sprint_task_steps_for_scrumboard(self):
response_data = {}
self.logger.debug("Inside update_sprint_task_steps_for_scrumboard")
update_sql = """
UPDATE tsprint_tasks
SET board_step=%s,
modified_by=%s,
modified_date=%s
WHERE task_no=%s
AND sprint_id=%s;
"""
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
for row in self.sprint_tasks:
self.logger.debug(row)
for k, v in row.items():
values = (
v,
self.modified_by,
self.modified_date,
k,
self.sprint_id,
)
self.logger.debug(f'Update : {update_sql} {values}')
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While update_sprint_task_steps_for_scrumboard {error}'
)
raise
class JirigoSprints(object):
def __init__(self, data={}):
print("Initializing JirigoSprints")
print(f'In for Create Sprints **** :{data}')
self.sprint_name = data.get('sprint_name', '')
self.project_name = data.get('project_name', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.sprint_tasks = data.get('sprint_tasks')
self.sprint_status = data.get('sprint_status')
self.sprint_id = data.get('sprint_id')
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.start_date = data.get('start_date')
self.end_date = data.get('end_date')
self.num_devs = data.get('num_devs')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_all_not_closed_tasks_byproj_for_sprint(self):
response_data = {}
self.logger.debug("Inside get_all_not_closed_tickets")
query_sql = """
WITH t AS (
SELECT task_int_id,
task_no,
summary,
issue_status,
issue_type,
severity,
priority,
module_name
FROM ttasks
WHERE project_id=get_proj_id(%s)
AND issue_status<>'Closed'
AND task_no NOT in
(select task_no
from tsprint_tasks
)
order by task_int_id
)
SELECT json_agg(t) from t;
"""
values = (self.project_name, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_not_closed_tickets {error}')
raise
def create_sprint_with_tasks(self):
response_data = {}
self.logger.debug("Sprints Service Inside Create Sprint")
create_sprint_sql = """INSERT INTO
tsprints (sprint_name,project_id,created_by,created_date,status)
values(%s,get_proj_id(%s),%s,%s,'Open')
returning sprint_id
"""
sprint_values = (
self.sprint_name,
self.project_name,
self.created_by,
self.created_date,
)
print("======sprint_values========")
print(sprint_values)
create_sprint_tasks_sql = """
INSERT INTO tsprint_tasks (sprint_id,task_no,created_by,workflow_step_id,created_date)
VALUES %s
"""
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(create_sprint_sql, sprint_values)
ret_sprint_id = cursor.fetchone()[0]
print(f'sprint_tasks {self.sprint_tasks}')
cursor.execute('select get_first_step_id_for_board_workflow(%s)',
(self.project_name, ))
first_step_id = cursor.fetchone()[0]
print(f'First Step id is {first_step_id}')
task_vals = [(ret_sprint_id, task, self.created_by, first_step_id)
for task in self.sprint_tasks]
print(f'task_vals {task_vals}')
execute_values(cursor,
create_sprint_tasks_sql,
task_vals,
template="(%s,%s,%s,%s,now())")
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) Sprint ID {ret_sprint_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"sprintId": ret_sprint_id,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Sprint Tasks {error}')
raise
def get_all_tasks_of_sprint(self):
response_data = {}
self.logger.debug("Sprints Inside get_all_not_closed_tickets")
query_sql = """
WITH t AS (
SELECT t.task_int_id,
t.task_no,
t.summary,
t.issue_status,
t.issue_type,
t.severity,
t.priority,
t.module_name,
ts.sprint_name
FROM ttasks t ,tsprint_tasks tt,tsprints ts
WHERE t.issue_status<>'Closed'
AND t.task_no=tt.task_no
AND tt.sprint_id =%s
AND tt.sprint_id =ts.sprint_id
order by t.task_int_id desc
)
SELECT json_agg(t) from t;
"""
values = (self.sprint_id, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) get_all_tasks_of_sprint ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_tasks_of_sprint {error}')
raise
def get_all_sprints_for_proj(self):
response_data = {}
self.logger.debug("Sprints Inside get_all_sprints_for_proj")
query_sql = """
WITH t AS (
SELECT sprint_id,
sprint_name,
get_proj_name(project_id) proj_name,
status,
TO_CHAR(start_date :: DATE, 'yyyy-mm-dd') start_date,
TO_CHAR(end_date :: DATE, 'yyyy-mm-dd') end_date,
num_devs
FROM tsprints
WHERE project_id=get_proj_id(%s)
order by sprint_id
)
SELECT json_agg(t) from t;
"""
values = (self.project_name, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Sprint Data {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_sprints_for_proj {error}')
raise
def update_sprint_details(self):
response_data = {}
self.logger.debug("Sprints Service Inside Update Sprint")
create_sprint_sql = """
UPDATE tsprints
SET sprint_name=%s,
status=%s,
start_date=%s,
end_date=%s,
modified_by=%s,
modified_date=%s,
num_devs=%s
WHERE sprint_id=%s;
"""
sprint_values = (
self.sprint_name,
self.sprint_status,
self.start_date,
self.end_date,
self.modified_by,
self.modified_date,
self.num_devs,
self.sprint_id,
)
print(sprint_values)
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(create_sprint_sql, sprint_values)
row_count = cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(
f'Insert Success with {row_count} row(s) Sprint ID {self.sprint_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"sprintId": self.sprint_id,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Sprint {error}')
raise
def update_sprint_tasks(self):
response_data = {}
self.logger.debug("Sprints Service Inside Update Sprint Tasks")
select_existing_sprint_tasks = """
WITH t AS (
SELECT task_no
FROM tsprint_tasks
WHERE sprint_id=%s
)
SELECT json_agg(t) from t;
"""
delete_all_sprint_tasks_sql = """
DELETE
FROM tsprint_tasks
WHERE sprint_id =%s
AND task_no NOT in %s
"""
sprint_tasksto_delete = (
self.sprint_id,
tuple(self.sprint_tasks),
)
print(sprint_tasksto_delete)
print("======sprint_values========")
print(
f'sprint_values {sprint_tasksto_delete} at time {self.created_date}'
)
self.logger.debug(
f'sprint_values {sprint_tasksto_delete} at time {self.created_date}'
)
create_sprint_tasks_sql = """
INSERT INTO tsprint_tasks (sprint_id,task_no,created_by,board_step,created_date)
VALUES %s
"""
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(select_existing_sprint_tasks, (self.sprint_id, ))
existing_sprint_tasks = json_data = cursor.fetchone()[0]
print(f'existing_sprint_tasks {existing_sprint_tasks}')
print(f'before sprint_tasks {self.sprint_tasks}')
for row in existing_sprint_tasks:
try:
self.sprint_tasks.remove(row['task_no'])
except ValueError:
print(f"{row['task_no']} deleted from app")
self.logger.debug(f"{row['task_no']} deleted from app")
cursor.execute(delete_all_sprint_tasks_sql, sprint_tasksto_delete)
row_count = cursor.rowcount
self.logger.debug(
f'Delete Success with {row_count} row(s) Sprint ID {self.sprint_id}'
)
print(f'after sprint_tasks {self.sprint_tasks}')
task_vals = [(self.sprint_id, x, self.created_by, self.sprint_id)
for x in self.sprint_tasks]
print(f'task_vals {task_vals}')
execute_values(
cursor,
create_sprint_tasks_sql,
task_vals,
template="(%s,%s,%s,get_first_step_for_sprint(%s),now())")
row_count = cursor.rowcount
self.logger.debug(
f'Bulk Insert Success with {row_count} row(s) Sprint ID {self.sprint_id} at time {self.created_date}'
)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"sprintId": self.sprint_id,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Sprint {error}')
raise
def get_data_for_sprint_gantt(self):
response_data = {}
self.logger.debug("Sprints Inside get_data_for_sprint_gantt")
query_sql = """
WITH t AS (
SELECT task_no,summary,start_date,end_date,get_user_name(assignee_id ) assignee,
estimated_time/60 duration,sprint_start_date ,sprint_end_date ,
get_sumof_task_actuals(task_no) actual_time
FROM v_sprint_details vsd
WHERE sprint_id=%s
ORDER BY start_date ,end_date,task_no
)
SELECT json_agg(t) FROM t;
"""
values = (self.sprint_id, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Sprint Data {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_data_for_sprint_gantt {error}')
raise
class JirigoTask(object):
def __init__(self, data={}):
print("Initializing JirigoTask")
print(f'In for Create/Update Task ****')
pprint(data)
self.task_int_id = data.get('task_int_id')
self.jdb = JirigoDBConn()
self.summary = data.get('summary', '')
self.description = data.get('description', '')
self.severity = data.get('severity', '')
self.priority = data.get('priority', '')
self.issue_type = data.get('issue_type', '')
self.issue_status = data.get('issue_status', '')
self.is_blocking = data.get('is_blocking', 'N')
self.environment = data.get('environment', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', '')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by')
self.reported_date = data.get('reported_date', datetime.datetime.now())
self.task_no = data.get('task_no', '-')
self.project_name = data.get('project_name', '')
self.project_id = data.get('project_id', '')
self.assignee_name = data.get('assignee_name', '')
self.module_name = data.get('module_name', '')
self.estimated_time = 0 # At the time of task creation est is 0
self.start_date = data.get('start_date', None)
self.end_date = data.get('end_date', None)
self.row_hash = data.get('row_hash', None)
self.assignee_id = data.get('assignee_id', None)
self.task_estimates = data.get('task_estimates', None)
self.logger = Logger()
def create_task(self):
response_data = {}
self.logger.debug("Inside Create Task")
insert_sql = """ INSERT INTO
TTASKS( task_no,summary,description,severity,priority,
issue_status,issue_type,environment,is_blocking,created_by,
created_date,reported_by,reported_date,assignee_id,project_id,module_name,
estimated_time,start_date,end_date)
VALUES (get_issue_no_by_proj(%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
get_user_id(%s),%s,get_user_id(%s),get_proj_id(%s),%s,%s,%s,%s) returning task_no;
"""
values = (self.project_name, self.summary, self.description,
self.severity, self.priority, "Open", self.issue_type,
self.environment, self.is_blocking, self.created_by,
datetime.datetime.today(), self.reported_by,
datetime.datetime.today(), self.assignee_name,
self.project_name, self.module_name, self.estimated_time,
self.start_date, self.end_date)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
task_no = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) Task ID {task_no}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": task_no,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Task {error}')
raise
def get_all_tasks(self):
response_data = {}
self.logger.debug("Inside get_all_tasks")
query_sql = """
WITH t AS (
SELECT task_int_id,
task_no,
summary,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date,
get_user_name(modified_by) modified_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') modified_date,
get_user_name(reported_by) reported_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') reported_date,
estimated_time/60 estimated_time,
get_user_name(assignee_id) assigned_to,
to_char(start_date, 'DD-Mon-YYYY') start_date,
to_char(end_date, 'DD-Mon-YYYY') end_date,
get_task_remaining_time(task_no) task_remaining_time
FROM ttasks
WHERE
project_id=COALESCE(%s,project_id) AND
(
created_by=COALESCE(%s,created_by) AND
COALESCE(assignee_id,-1)=COALESCE(%s,COALESCE(assignee_id,-1)) AND
COALESCE(modified_by,-1)=COALESCE(%s,COALESCE(modified_by,-1))
)
order by task_int_id
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
self.assignee_id = None if self.assignee_id == '' else self.assignee_id
self.created_by = None if self.created_by == '' else self.created_by
self.modified_by = None if self.modified_by == '' else self.modified_by
values = (
self.project_id,
self.created_by,
self.assignee_id,
self.modified_by,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Task {error}')
raise
def get_task_details(self):
self.logger.debug('Inside get_task_details')
response_data = {}
query_sql = """
WITH t AS
(SELECT task_int_id,
task_no,
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
get_proj_name(project_id) project_name,
get_user_name(COALESCE(assignee_id, 0)) assignee_name,
get_user_name(COALESCE(created_by, 0)) created_by,
created_date,
get_user_name(COALESCE(modified_by, 0)) modified_by,
modified_date,
get_user_name(COALESCE(reported_by, 0)) reported_by,
reported_date,
estimated_time/60 estimated_time,
start_date,
end_date,
get_task_remaining_time(task_no) task_remaining_time,
row_hash,
get_sprint_name_for_task(task_no) as sprint_name
FROM ttasks
WHERE TASK_NO=%s )
SELECT json_agg(t)
FROM t;
"""
values = (self.task_no, )
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
print(
f'Select Success with {row_count} row(s) Task ID {json_data}')
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Task :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise
def update_task(self):
response_data = {}
self.logger.debug("Inside Update Task update_tasks")
update_sql = """
UPDATE TTASKS
SET summary=%s,
description=%s,
severity=COALESCE(%s,severity),
priority=COALESCE(%s,priority),
issue_status=COALESCE(%s,issue_status),
issue_type=COALESCE(%s,issue_type),
environment=COALESCE(%s,issue_type),
modified_by=%s,
modified_date=%s,
reported_by=COALESCE(get_user_id(%s),reported_by),
reported_date=%s,
project_id=get_proj_id(%s),
assignee_id=get_user_id(%s),
is_blocking=%s,
module_name=%s,
start_date=%s,
end_date=%s
WHERE task_no=%s and row_hash=%s;
"""
self.estimated_time = None if self.estimated_time == '' else self.estimated_time
values = (
self.summary,
self.description,
self.severity,
self.priority,
self.issue_status,
self.issue_type,
self.environment,
self.modified_by,
datetime.datetime.today(),
self.reported_by,
datetime.datetime.today(),
self.project_name,
self.assignee_name,
self.is_blocking,
self.module_name,
self.start_date,
self.end_date,
self.task_no,
self.row_hash,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
print(dir(cursor))
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": cursor.rowcount
}
print(response_data)
print('-' * 80)
self.jdb.dbConn.commit()
if response_data['dbQryResponse']['rowCount'] == 0:
response_data['dbQryStatus'] = 'FailureNoRowFound'
else:
response_data['dbQryStatus'] = 'Success'
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Task {error}')
raise
def clone_task(self):
response_data = {}
new_task_no = 'Error'
self.logger.debug("Inside Update Task update_tasks")
insert_sql = """
INSERT INTO ttasks (task_no, SUMMARY, description, issue_status, issue_type,
severity, priority, environment, is_blocking, module_name,created_by,
created_date, reported_by, reported_date, project_id,estimated_time,
start_date,end_date,row_hash)
SELECT get_issue_no_by_proj(get_proj_name(project_id)),
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
%s,
%s,
reported_by,
reported_date,
project_id,
estimated_time,
start_date,
end_date,
row_hash
FROM ttasks
WHERE task_no=%s
returning task_no;
"""
values = (
self.created_by,
datetime.datetime.today(),
self.task_no,
)
self.logger.debug(f'Update : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
new_task_no = cursor.fetchone()[0]
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"clonedTaskNo": new_task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While clone_task Task {error}')
raise
def update_task_assignee(self):
response_data = {}
self.logger.debug("Inside update_task_assignee")
update_sql = """
UPDATE TTASKS
SET assignee_id=%s,
modified_by=%s,
modified_date=%s
WHERE task_no=%s;
"""
values = (
self.assignee_id,
self.modified_by,
self.modified_date,
self.task_no,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While update_task_assignee {error}')
raise
def update_task_status(self):
response_data = {}
self.logger.debug("Inside update_task_status")
update_sql = """
UPDATE TTASKS
SET issue_status=%s,
modified_by=%s,
modified_date=%s
WHERE task_no=%s;
"""
values = (
self.issue_status,
self.modified_by,
self.modified_date,
self.task_no,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While update_task_status {error}')
raise
def create_update_task_estimates(self):
response_data = {}
t_task_estimated_time = 0
self.logger.debug("create_update_task_estimates")
set_task_estimate_to_zero = """
UPDATE ttasks
SET estimated_time=0
WHERE task_no=%s
"""
t_task_no = (self.task_no, )
clean_up_task_estimates_sql = """
DELETE
FROM ttask_estimates
WHERE task_no=%s
"""
t_task_no = (self.task_no, )
print(f'task No {t_task_no}')
self.logger.debug(f'task No {t_task_no}')
ins_task_estimate_sql = """
INSERT INTO ttask_estimates (task_no,activity,estimated_time)
VALUES %s
"""
get_task_estimate_sql = """
SELECT estimated_time/60 estimated_time
FROM ttasks
WHERE task_no=%s
"""
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(set_task_estimate_to_zero, t_task_no)
row_count = cursor.rowcount
print(f'Task estimate set to zero OK {row_count}')
self.logger.debug(f'Task estimate set to zero OK {row_count}')
cursor.execute(clean_up_task_estimates_sql, t_task_no)
row_count = cursor.rowcount
print(f'Delete Success with {row_count} row(s) ID {self.task_no}')
self.logger.debug(
f'Delete Success with {row_count} row(s) ID {self.task_no}')
print(self.task_estimates)
estimate_values = [(self.task_no, x['activity'],
x['estimated_time'])
for x in self.task_estimates]
print(f'estimate_values {estimate_values}')
execute_values(cursor,
ins_task_estimate_sql,
estimate_values,
template="(%s,%s,%s)")
row_count = cursor.rowcount
self.logger.debug(
f'Bulk Insert Success with {row_count} row(s) ID {self.task_no}'
)
self.jdb.dbConn.commit()
cursor.execute(get_task_estimate_sql, t_task_no)
row_count = cursor.rowcount
t_task_estimated_time = cursor.fetchone()[0]
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"rowCount": row_count,
"taskEstimatedTime": t_task_estimated_time
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While creating estimates {error}')
raise
def get_task_estimates(self):
self.logger.debug('Inside get_task_estimates')
response_data = {}
query_sql = """
WITH t AS
(SELECT *
FROM ttask_estimates
WHERE task_no=%s )
SELECT json_agg(t)
FROM t;
"""
values = (self.task_no, )
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
print(
f'Select Success with {row_count} row(s) Task ID {json_data}')
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Task :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise
class JirigoLinkTaskTicket(object):
def __init__(self, data={}):
print("Initializing JirigoLinkTaskTicket")
pprint(data)
self.jdb = JirigoDBConn()
self.project_id = data.get('project_id', 0)
self.search_term = data.get('search_term', 0)
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
#-----------
self.item_no = data.get('item_no', '')
self.relationship = data.get('relationship', '')
self.related_items = data.get('related_items', '')
#-----------
self.logger = Logger()
def create_tasks_tickets_links(self):
response_data = {}
rows = []
self.logger.debug("Inside create_link ")
insert_sql = """ INSERT INTO
tticket_and_task_links( ticket_or_task_no,relationship,linked_ticket_or_task_no,
created_by,created_date)
values %s
"""
print('=' * 80)
print(self.related_items)
print(type(self.related_items))
rows = [(self.item_no, self.relationship, item['code'],
self.created_by, self.created_date)
for item in self.related_items]
print(rows)
print('=' * 80)
self.logger.debug(f'Insert : {insert_sql} vals {rows}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
execute_values(cursor,
insert_sql,
rows,
template=None,
page_size=100)
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(f'Insert Success with {row_count} row(s) ')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowCount": row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Link {error}')
raise
def get_task_or_ticket_depends_on(self):
response_data = {}
self.logger.debug("Inside get_task_or_ticket_depends_on")
query_sql = """
WITH t AS (
SELECT vatt.*,tatl.*
FROM tticket_and_task_links tatl
INNER JOIN
v_all_tickets_tasks vatt
ON tatl.linked_ticket_or_task_no =vatt.item_no
AND tatl.relationship='Depends On'
AND tatl.ticket_or_task_no=%s
AND vatt.project_id=%s
)
SELECT json_agg(t) from t;
"""
values = (
self.item_no,
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_task_or_ticket_depends_on {error}')
raise
def get_task_or_ticket_related_to(self):
response_data = {}
self.logger.debug("Inside get_task_or_ticket_related_to")
query_sql = """
WITH t AS (
SELECT vatt.*,tatl.*
FROM tticket_and_task_links tatl
INNER JOIN
v_all_tickets_tasks vatt
ON tatl.linked_ticket_or_task_no =vatt.item_no
AND tatl.relationship='Related To'
AND tatl.ticket_or_task_no=%s
AND vatt.project_id=%s
)
SELECT json_agg(t) from t;
"""
values = (
self.item_no,
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_task_or_ticket_related_to {error}')
raise
def get_task_or_ticket_duplicted_by(self):
response_data = {}
self.logger.debug("Inside get_task_or_ticket_duplicted_by")
query_sql = """
WITH t AS (
SELECT vatt.*,tatl.*
FROM tticket_and_task_links tatl
INNER JOIN
v_all_tickets_tasks vatt
ON tatl.linked_ticket_or_task_no =vatt.item_no
AND tatl.relationship='Duplicated By'
AND tatl.ticket_or_task_no=%s
AND vatt.project_id=%s
)
SELECT json_agg(t) from t;
"""
values = (
self.item_no,
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_task_or_ticket_duplicted_by {error}')
raise
def get_tasks_tickets_for_multiselect_drop_down(self):
response_data = {}
self.logger.debug("Inside get_all_tasks")
query_sql = """
WITH t AS (
SELECT item_no,substring(summary,0,80) summary
FROM v_all_tickets_tasks vatt
WHERE project_id=%s
AND ( lower(item_no) LIKE LOWER(%s)
OR lower(summary) LIKE LOWER(%s)
)
order by order_no
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
f'%{self.search_term}%',
f'%{self.search_term}%',
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_tasks_tickets_for_multiselect_drop_down Success with {row_count} row(s) '
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_tasks_tickets_for_multiselect_drop_down {error}'
)
raise
class JirigoRoles(object):
def __init__(self,data={}):
print("Initializing JirigoRoles")
self.project_id=data.get('project_id','')
self.project_name=data.get('project_name','')
self.role_id=data.get('role_id','')
self.workflow_id=data.get('workflow_id','')
self.role_name=data.get('role_name','')
self.user_id=data.get('user_id','')
self.new_roles_values=data.get('new_roles_values','')
self.is_active=data.get('is_active','')
self.project_name=data.get('project_name','')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by=data.get('modified_by')
self.modified_date=datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_all_active_roles(self):
response_data={}
self.logger.debug("Inside get_all_roles")
query_sql="""
WITH t AS (
SELECT role_id,
role_name,
created_date,
get_user_name(created_by) created_by,
modified_date,
get_user_name(modified_by) modified_by,
is_active
FROM troles
WHERE is_active='Y'
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql} ')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_roles {error}')
raise
def get_roles_active_for_allprojects(self):
response_data={}
self.logger.debug("Inside get_active_project_roles")
query_sql="""
WITH t AS (
SELECT
tr.role_id,
tr.role_name,
tr.is_active,
tpr.project_id ,
get_proj_name(tpr.project_id) project_name,
get_workflow_name (tprw.workflow_id ) workflow_name
FROM troles tr
INNER JOIN tproject_roles tpr ON
tr.role_id = tpr.role_id
LEFT OUTER JOIN tproj_role_workflow tprw ON
tr.role_id = tprw.role_id
WHERE
tr.is_active = 'Y'
order by tr.role_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_active_project_roles {error}')
raise
def add_project_role(self):
response_data={}
self.logger.debug("add_project_role ")
insert_proj_role_sql="""INSERT
INTO tproject_roles
(project_id, role_id,
created_by, created_date)
VALUES (%s,%s,
%s,%s);
"""
values_create_workflow=(self.project_id,self.role_id,self.created_by,self.created_date,)
self.logger.debug(f'{insert_proj_role_sql} values {values_create_workflow}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_proj_role_sql,values_create_workflow)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Insert Success with {row_count} row(s)')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While add_project_role {error}')
raise
def remove_project_role(self):
response_data={}
self.logger.debug("remove_project_role ")
del_proj_role="""DELETE FROM tproject_roles
WHERE project_id=%s
AND role_id=%s
"""
values=(self.project_id,self.role_id,)
self.logger.debug(f'{del_proj_role} values {values}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(del_proj_role,values)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Delete Success with {row_count} row(s)')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While remove_project_role {error}')
raise
def add_role(self):
response_data={}
self.logger.debug("add_role ")
del_proj_role=""" INSERT
INTO troles
(role_id,
role_name,is_active,created_by,created_date)
VALUES ((
select max(role_id)+1 from troles
),
%s,%s,%s,%s)
returning role_id;
"""
values=(self.role_name,'Y',self.created_by,self.created_date,)
self.logger.debug(f'{del_proj_role} values {values}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(del_proj_role,values)
role_id=cursor.fetchone()[0]
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Insert Success with {row_count} row(s) and role_id {role_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"role_id":role_id,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While add_role {error}')
raise
def update_role(self):
response_data={}
self.logger.debug("remove_project_role ")
upd_proj_role_sql=""" UPDATE troles
SET is_active=%s,
role_name=%s,
modified_by=%s,
modified_date=%s
WHERE role_id=%s;
"""
values_create_workflow=(self.is_active,self.role_name,self.modified_by,self.modified_date,self.role_id,)
self.logger.debug(f'{upd_proj_role_sql} values {values_create_workflow}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(upd_proj_role_sql,values_create_workflow)
self.jdb.dbConn.commit()
row_count=cursor.rowcount
self.logger.debug(f'Update Success with {row_count} row(s)')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While update_role {error}')
raise
def del_role(self):
response_data={}
self.logger.debug("add_role ")
del_role=""" UPDATE troles
SET is_active='N',
modified_by=%s,
modified_date=%s
WHERE role_id=%s;
"""
values=(self.modified_by,self.modified_date,self.role_id,)
self.logger.debug(f'{del_role} values {values}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(del_role,values)
role_id=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Delete Success with {row_count} row(s) and role_id {role_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"role_id":role_id,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While del_role {error}')
raise
def assign_workflow_to_role(self):
response_data={}
self.logger.debug("assign_workflow_to_role ")
assign_wf_to_role_sql=""" INSERT
INTO tproj_role_workflow
(project_id,role_id,workflow_id,created_by,created_date)
VALUES (%s,%s,%s,%s,%s )
"""
values=(self.project_id,self.role_id,self.workflow_id,self.created_by,self.created_date,)
self.logger.debug(f'{assign_wf_to_role_sql} values {values}')
try:
print('#'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(assign_wf_to_role_sql,values)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Insert Success with {row_count} row(s) ')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While assign_workflow_to_role {error}')
raise
def get_roles_for_user_assignment(self):
#Get all the assigned and unassigned roles to a user for a project
response_data={}
self.logger.debug("Inside get_roles_for_user_assignment")
query_sql="""
WITH t AS (
SELECT tu.user_id,tu.email,tpr.project_id ,tpr.role_id ,
get_role_name(tpr.role_id) role_name,
get_proj_name(tpr.project_id) project_name,
toj.*
FROM tusers tu
INNER JOIN tuser_projects tup
ON tu.user_id =tup.user_id
INNER JOIN tproject_roles tpr
ON tup.project_id =tpr.project_id
LEFT OUTER JOIN (
SELECT tu.user_id assigned_user_id,tu.email ,tup.project_id assigned_proj_id,
tupr.is_default ,tupr.role_id assigned_role_id
FROM tusers tu
INNER JOIN tuser_projects tup
ON tu.user_id =tup.user_id
INNER JOIN tuserproject_roles tupr
ON tu.user_id =tupr.user_id
AND tupr.project_id =tup.project_id
) toj
ON tpr.project_id =toj.assigned_proj_id
AND tpr.role_id =toj.assigned_role_id
AND tu.user_id =toj.assigned_user_id
WHERE tu.user_id=%s
AND tup.project_id =%s
)
SELECT json_agg(t) from t;
"""
values=(self.user_id,self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_roles_for_user_assignment {error}')
raise
def assign_roles_to_user(self):
response_data={}
self.logger.debug("assign_roles_to_user")
delete_all_menus_for_role="""
DELETE
FROM tuserproject_roles
WHERE user_id=%s
AND project_id=%s
"""
role_values=(self.user_id,self.project_id,)
print(role_values)
print("======role_values========")
print(f'role_values {role_values} at time {self.created_date}')
self.logger.debug(f'role_values {role_values} at time {self.created_date}')
assign_roles_to_user="""
INSERT INTO tuserproject_roles (user_id,project_id,role_id,is_default,created_by,created_date)
VALUES %s
"""
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(delete_all_menus_for_role,role_values)
row_count=cursor.rowcount
self.logger.debug(f'Delete Success with {row_count} row(s) ID {self.role_id}')
print(f'new_roles_values {self.new_roles_values}')
role_vals=[(x['user_id'],x['project_id'],x['role_id'],'Y' if x['is_default'] else 'N',self.created_by,self.created_date) for x in self.new_roles_values]
print(f'menu_vals {role_vals}')
execute_values(cursor,assign_roles_to_user,role_vals,template="(%s,%s,%s,%s,%s,%s)")
row_count=cursor.rowcount
self.logger.debug(f'Bulk Insert Success with {row_count} row(s) ID {self.role_id} at time {self.created_date}')
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"dataAdded":self.new_roles_values,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Assigning Roles to user {error}')
raise
class JirigoSprintDashboard(object):
def __init__(self,data={}):
print("Initializing JirigoSprintDashboard ")
print(f'In for Create Sprints **** :{data}')
# self.sprint_name=data.get('sprint_name','')
# self.project_name=data.get('project_name','')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
# self.sprint_tasks=data.get('sprint_tasks')
# self.sprint_status=data.get('sprint_status')
self.sprint_id=data.get('sprint_id')
self.project_id=data.get('project_id')
self.sprint_tasks=data.get('sprint_tasks')
self.modified_by=data.get('modified_by')
self.modified_date=datetime.datetime.now()
# self.start_date=data.get('start_date')
# self.end_date=data.get('end_date')
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_all_tasks_attribute_summary(self):
response_data={}
self.logger.debug("Sprints Inside get_all_tasks_attribute_summary")
query_sql="""
WITH tc AS (SELECT json_object_agg(DISTINCT t.priority,t.tot_priority_cnt) priorities,
json_object_agg(DISTINCT t.severity,t.tot_severity_cnt) severities,
json_object_agg(DISTINCT t.issue_type,t.tot_issue_type_cnt) issue_types,
json_object_agg(DISTINCT t.issue_status,t.tot_issue_status_cnt) issue_statuses
FROM (
SELECT priority,severity ,issue_type ,issue_status,
count(priority) over(partition BY priority) tot_priority_cnt,
count(severity) over(partition BY severity) tot_severity_cnt,
count(issue_type ) over(partition BY issue_type) tot_issue_type_cnt,
count(issue_status ) over(partition BY issue_status) tot_issue_status_cnt
FROM v_sprint_details vsd
WHERE sprint_id=%s ) t
)
select json_agg(tc) from tc;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_all_tasks_attribute_summary ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_tasks_attribute_summary {error}')
raise
def get_sprint_efforts_summary(self):
response_data={}
self.logger.debug("Sprints Inside get_sprint_efforts_summary")
query_sql="""
WITH tc AS (SELECT
sprint_id,
sprint_name,
sprint_start_date ,
sprint_end_date ,
round(CAST(sum(estimated_time)/60 AS NUMERIC),2) tot_est,
sum(task_actuals)tot_act,
count(*) tot_task_count,
round(CAST(sum(estimated_time)/60-sum(task_actuals) as Numeric),2) tot_rem
FROM v_sprint_details
WHERE sprint_id=%s
GROUP BY sprint_id,sprint_name,sprint_start_date ,
sprint_end_date )
select json_agg(tc) from tc;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_sprint_efforts_summary ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_sprint_efforts_summary {error}')
raise
def get_sprint_burndown_chart_data(self):
response_data={}
self.logger.debug("Sprints Inside get_sprint_burndown_chart_data")
query_sql="""
WITH jt AS (
WITH bdt as(
WITH gen_days AS (
SELECT t2.sprint_id,t2.sprint_name,t2.num_devs,generate_series(t2.start_date::timestamptz ,t2.end_date::timestamptz ,'1 days' ) c_date
,(
SELECT round(CAST(sum(tt.estimated_time)/60 AS NUMERIC),2)
FROM ttasks tt
INNER JOIN tsprint_tasks tst
ON tt.task_no=tst.task_no
WHERE tst.sprint_id=t2.sprint_id
) tot_est
FROM tsprints t2
WHERE sprint_id=%s)
SELECT gen_days.sprint_id,gen_days.c_date, gen_days.tot_est,
CASE to_char(gen_days.c_date,'Dy')
WHEN 'Sat' THEN 0
WHEN 'Sun' THEN 0
ELSE 8
END AS work_hours,
get_sumof_sprint_actuals_bydate(gen_days.sprint_id,to_char(gen_days.c_date,'dd-Mon-YYYY')) AS actuals,
gen_days.num_devs
FROM gen_days
)
SELECT bdt.sprint_id,bdt.c_date,bdt.tot_est,bdt.work_hours,
to_char(bdt.c_date,'dd-Mon') c_date2,
to_char(bdt.c_date,'dd-Mon-YYYY') c_date3,
COALESCE (lag(tot_est,1) over(ORDER BY bdt.c_date),bdt.tot_est) tot_est,
sum(bdt.work_hours) over(ORDER BY bdt.c_date)*bdt.num_devs wk_hrs,
COALESCE(lag(tot_est,1) over(ORDER BY bdt.c_date)-sum(bdt.work_hours) over(ORDER BY bdt.c_date)*bdt.num_devs,bdt.tot_est) ideal_line,
bdt.tot_est- sum(bdt.actuals) OVER (ORDER BY bdt.c_date) actuals
FROM bdt)
select json_agg(jt) from jt;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('~'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_sprint_burndown_chart_data ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_sprint_burndown_chart_data {error}')
raise
def get_sprint_workload_by_user(self):
response_data={}
self.logger.debug("Sprints Inside get_sprint_workload_by_user")
query_sql="""
WITH tc AS ( SELECT get_user_name(assignee_id ) user_name,
round(CAST(sum(estimated_time)/60 AS NUMERIC),2) estimated_time
FROM v_sprint_details
WHERE sprint_id=%s
GROUP BY get_user_name(assignee_id )
order by estimated_time desc
)
select json_agg(tc) from tc;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_sprint_workload_by_user {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_sprint_workload_by_user {error}')
raise
def get_sprint_num_tasks_by_user(self):
response_data={}
self.logger.debug("Sprints Inside get_sprint_num_tasks_by_user")
query_sql="""
WITH tc AS (SELECT get_user_name(assignee_id ) user_name,count(*) cnt
FROM v_sprint_details
WHERE sprint_id=%s
GROUP BY get_user_name(assignee_id )
ORDER BY 2 DESC
)
select json_agg(tc) from tc;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_sprint_num_tasks_by_user {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_sprint_num_tasks_by_user {error}')
raise
def get_task_actuals_by_activity(self):
response_data={}
self.logger.debug("Sprints Inside get_task_actuals_by_activity")
query_sql="""
WITH tc AS (SELECT activity ,sum(actual_time_spent)/60 tot_act
FROM ttask_actuals
WHERE task_no in (SELECT task_no
FROM v_sprint_details vsd
WHERE sprint_id=%s )
GROUP BY activity
ORDER BY 2 DESC
)
select json_agg(tc) from tc;
"""
values=(self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_task_actuals_by_activity {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_task_actuals_by_activity {error}')
raise
def get_task_estimated_vs_actual_efforts(self):
response_data={}
self.logger.debug("Sprints Inside get_task_estimated_vs_actual_efforts")
query_sql="""
WITH tc AS (
WITH
ts_act AS (
SELECT activity ,sum(actual_time_spent)/60 tot_act
FROM ttask_actuals
WHERE task_no in (SELECT task_no
FROM v_sprint_details vsd
WHERE sprint_id=%s )
GROUP BY activity
),
ts_est AS (
SELECT activity ,sum(estimated_time )/60 tot_est
FROM ttask_estimates te
WHERE task_no in (SELECT task_no
FROM v_sprint_details vsd
WHERE sprint_id=%s )
GROUP BY activity
)
SELECT COALESCE (ts_est.activity,COALESCE (ts_act.activity,ts_est.activity)) activity
,ts_est.tot_est,ts_act.tot_act
FROM ts_est
FULL OUTER JOIN ts_act ON ts_est.activity=ts_act.activity
ORDER BY 1
)
select json_agg(tc) from tc;
"""
values=(self.sprint_id,self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_task_estimated_vs_actual_efforts {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_task_estimated_vs_actual_efforts {error}')
raise
def get_count_of_task_in_current_status(self):
response_data={}
self.logger.debug("Sprints Inside get_count_of_task_in_current_status")
query_sql="""
WITH tc AS (
WITH task_status_order AS (
SELECT ref_value,order_id FROM tref_master tm
WHERE ref_category ='TASKS'
AND ref_name='Status'
AND project_id =%s
ORDER BY order_id
),
sprint_task_statuses AS (
SELECT vsd.issue_status,count(*) issue_count
FROM v_sprint_details vsd
WHERE vsd.sprint_id =%s
GROUP BY vsd.issue_status
)
SELECT tso.*,sts.*
FROM task_status_order tso, sprint_task_statuses sts
WHERE sts.issue_status=tso.ref_value
ORDER BY tso.order_id
)
select json_agg(tc) from tc;
"""
values=(self.project_id,self.sprint_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) get_task_count_by_status {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_task_count_by_status {error}')
raise
class JirigoVacations(object):
def __init__(self,data={}):
print("Initializing JirigoVacations")
print(f'In for Create/Update/Delete vacations ****')
pprint(data)
self.project_id = data.get('project_id')
self.vacation_id = data.get('vacation_id')
self.user_id = data.get('user_id')
self.start_date = data.get('start_date')
self.end_date = data.get('end_date')
self.input_date = data.get('input_date')
self.description = data.get('description')
self.created_date=datetime.datetime.now()
self.modified_date=datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_all_vacations_for_user(self):
response_data={}
self.logger.debug("Inside get_all_vacations_for_user")
query_sql="""
with t as (
SELECT *
FROM tvacations
WHERE user_id=%s
)
select json_agg(t) from t ;
"""
self.logger.debug(f'Select all vacation : {query_sql}')
values=(self.user_id,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'get_all_vacations_for_user Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data if json_data != None else 'No Rows Fetched'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_vacations_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_vacations_for_user {error}')
raise
def get_vacation_for_user_by_timerange(self):
response_data={}
self.logger.debug("Inside get_vacation_for_user_by_timerange")
query_sql="""
with t as (
SELECT *
FROM tvacations
WHERE user_id=%s
AND %s between start_date and end_date
)
select json_agg(t) from t ;
"""
values=(self.user_id,self.input_date,)
self.logger.debug(f'Select all vacation : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'get_vacation_for_user_by_timerange Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_vacation_for_user_by_timerange {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_vacation_for_user_by_timerange {error}')
raise
def get_all_vacations_by_timerange(self):
response_data={}
self.logger.debug("Inside get_all_vacations_by_timerange")
query_sql="""
with t as (
SELECT vacation_id,
user_id,
get_user_name(user_id),
start_date,
end_date,
description
FROM tvacations
WHERE start_date >=%s
and end_date <=%s
)
select json_agg(t) from t ;
"""
values=(self.start_date,self.end_date,)
self.logger.debug(f'Select all vacation : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'get_all_vacations_by_timerange Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_vacations_by_timerange {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_vacations_by_timerange {error}')
raise
def cre_vacation_for_user_by_timerange(self):
response_data={}
self.logger.debug("Inside cre_vacation_for_user_by_timerange")
query_sql="""
INSERT INTO
tvacations (user_id,start_date,end_date,description,created_date)
VALUES (%s,%s,%s,%s,%s)
RETURNING vacation_id
"""
self.logger.debug(f'Select all vacation : {query_sql}')
values=(self.user_id,self.start_date,self.end_date,self.description,self.created_date,)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
self.jdb.dbConn.commit()
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'cre_vacation_for_user_by_timerange Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Vacation Created '+str(json_data)
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While cre_vacation_for_user_by_timerange {error}')
if(self.jdb.dbConn):
print(f'Error While cre_vacation_for_user_by_timerange {error}')
raise
def del_vacation_for_user(self):
response_data={}
self.logger.debug("Inside del_vacation_for_user")
query_sql="""
DELETE FROM tvacations WHERE VACATION_ID=%s
"""
self.logger.debug(f'Select all vacation : {query_sql}')
values=(self.vacation_id)
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
row_count=cursor.rowcount
self.logger.debug(f'del_vacation_for_user Select Success with {row_count} row(s) data')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Record Deleted'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference del_vacation_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference del_vacation_for_user {error}')
raise
def upd_vacation_for_user(self):
response_data={}
self.logger.debug("Inside del_vacation_for_user")
query_sql="""
UPDATE tvacations
SET start_date=%s,
end_date=%s,
description=%s,
modified_date=%s
WHERE vacation_id=%s
"""
values=(self.start_date,self.end_date,self.description,self.modified_date,self.vacation_id,)
self.logger.debug(f'Select all vacation : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
row_count=cursor.rowcount
self.logger.debug(f'del_vacation_for_user Select Success with {row_count} row(s) data')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']='Record Deleted'
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference del_vacation_for_user {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference del_vacation_for_user {error}')
raise
def check_vacation_overlap(self):
response_data={}
self.logger.debug("Inside check_vacation_overlap")
query_sql="""
with t as (
SELECT *
FROM tvacations
WHERE user_id=%s
AND %s between start_date and end_date
)
select json_agg(t) from t ;
"""
values=(self.user_id,self.input_date,)
self.logger.debug(f'Select all vacation : {query_sql} Values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'check_vacation_overlap Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference check_vacation_overlap {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference check_vacation_overlap {error}')
raise
class JirigoTicketComments(object):
def __init__(self, data):
print("Initializing JirigoUsers")
pprint(data)
self.ticket_no = data.get('ticket_no', '')
self.comment = data.get('comment', '')
self.created_by = data.get('created_by', '1')
self.created_date = data.get('created_date', datetime.datetime.now())
self.jdb = JirigoDBConn()
self.logger = Logger()
def create_comment(self):
response_data = {}
print("Inside Create User")
insert_sql = """ INSERT INTO TTICKET_COMMENTS(ticket_no,comment,created_by,created_date)
VALUES (%s,%s,%s,%s) returning comment_id;
"""
values = (
self.ticket_no,
self.comment,
self.created_by,
datetime.datetime.today(),
)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
comment_id = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Ticket Comment Creation Success with {row_count} row(s) User ID {comment_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"commentId": comment_id,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(
f'Error While Creating Ticket Comment {error}')
print(f'Error While Creating Ticket Comment {error}')
raise
def get_tickets_all_comments(self):
response_data = {}
self.logger.debug("Inside get_all_users")
query_sql = """
WITH t AS (
select comment_id,ticket_no,comment,get_user_name(created_by) created_by,
created_date
from tticket_comments
where is_active='Y'
and ticket_no=%s
order by comment_id
)
SELECT json_agg(t) from t;
"""
values = (self.ticket_no, )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Ticket Comments Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(
f'get_tickets_all_comments Error While Select Ticket Comments {error}'
)
print(
f'get_tickets_all_comments Error While Select Ticket Comments {error}'
)
raise
class JirigoTicketLogTime(object):
def __init__(self, data):
print("Initializing JirigoTicketLogTime")
pprint(data)
self.ticket_no = data.get('ticket_no', '')
self.activity = data.get('activity', '')
self.actual_time_spent = data.get('actual_time_spent', 0)
self.other_activity_comment = data.get('other_activity_comment', '')
self.timelog_comment = data.get('timelog_comment', '')
self.time_spent_by = data.get('time_spent_by', '1')
self.actual_date = data.get('actual_date', datetime.datetime.now())
self.jdb = JirigoDBConn()
self.logger = Logger()
def create_timelog_entry(self):
response_data = {}
print("Inside Create Comment")
insert_sql = """ INSERT
INTO tticket_actuals(ticket_no,activity,time_spent,actual_date,
time_spent_by,other_activity_comment,timelog_comment)
VALUES (%s,%s,%s,%s,%s,%s,%s) returning actuals_id;
"""
values = (self.ticket_no, self.activity, self.actual_time_spent,
self.actual_date, self.time_spent_by,
self.other_activity_comment, self.timelog_comment)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
actuals_id = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Ticket Comment Creation Success with {row_count} row(s) User ID {actuals_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"actualsId": actuals_id,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(f'Error create_timelog_entry {error}')
print(f'Error create_timelog_entry {error}')
raise
def get_timelog_entries_for_ticket(self):
response_data = {}
self.logger.debug("Inside get_timelog_entries_for_ticket")
query_sql = """
WITH t AS (
SELECT ticket_no,activity,time_spent,actual_date,
get_user_name(time_spent_by) time_spent_by,
other_activity_comment,timelog_comment
FROM tticket_actuals
WHERE ticket_no=%s
order by actual_date desc
)
SELECT json_agg(t) from t;
"""
values = (self.ticket_no, )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_timelog_entries_for_ticket Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(
f'get_timelog_entries_for_ticket Error While Select Ticket Comments {error}'
)
print(
f'get_timelog_entries_for_ticket Error While Select Ticket Comments {error}'
)
raise
class JirigoTicketDashboard(object):
def __init__(self, data={}):
print("Initializing JirigoTicketDashboard")
print(f'In for Create Dashboard **** :{data}')
self.project_id = data.get('project_id', 0)
self.last_n_days = data.get('last_n_days', 0)
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_ticket_dashboard_generic_summary(self):
response_data = {}
self.logger.debug("Inside get_ticket_audit")
query_sql = """
WITH t AS(
SELECT 'issueStatus' col_header,
issue_status col_ref_name,
count(*) cnt
FROM ttickets t
WHERE project_id= %s AND created_date >= now() - INTERVAL %s
GROUP BY issue_status
UNION ALL
SELECT 'issueType' col_header,
t.issue_type,
count(*) cnt
FROM
(SELECT CASE issue_type
WHEN 'Bug' THEN 'Bug'
ELSE 'Others'
END AS issue_type
FROM ttickets t2
WHERE project_id=%s AND t2.created_date >= now() - INTERVAL %s) AS t
GROUP BY issue_type
UNION ALL
SELECT 'Severity' col_header,
severity,
count(*) cnt
FROM ttickets t
WHERE SEVERITY IN ('High',
'Critical')
AND project_id=%s AND created_date >= now() - INTERVAL %s
GROUP BY severity)
SELECT json_object_agg(col_header||col_ref_name, cnt)
FROM t ;
"""
interval_val = f'{self.last_n_days} days' if int(
self.last_n_days) > 1 else f'{self.last_n_days} day'
values = (
self.project_id,
interval_val,
self.project_id,
interval_val,
self.project_id,
interval_val,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_ticket_dashboard_generic_summary Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While getting get_ticket_dashboard_generic_summary {error}'
)
raise
def get_ticket_summary_by_issue_status(self):
response_data = {}
self.logger.debug("Inside get_ticket_summary_by_issue_status")
query_sql = """
WITH t AS(
SELECT issue_status,count(*) count
FROM ttickets t
WHERE project_id =%s
AND created_date >= now() - INTERVAL %s
GROUP BY issue_status
order by count desc
)
SELECT json_agg(t)
FROM t ;
"""
interval_val = f'{self.last_n_days} days' if int(
self.last_n_days) > 1 else f'{self.last_n_days} day'
values = (
self.project_id,
interval_val,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_ticket_summary_by_issue_status Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_ticket_summary_by_issue_status {error}')
raise
def get_ticket_summary_by_issue_type(self):
response_data = {}
self.logger.debug("Inside get_ticket_summary_by_issue_type")
query_sql = """
WITH t AS(
SELECT issue_type,count(*) count
FROM ttickets t
WHERE project_id =%s
AND created_date >= now() - INTERVAL %s
GROUP BY issue_type
order by count desc
)
SELECT json_agg(t)
FROM t ;
"""
interval_val = f'{self.last_n_days} days' if int(
self.last_n_days) > 1 else f'{self.last_n_days} day'
values = (
self.project_id,
interval_val,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_ticket_summary_by_issue_type Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_ticket_summary_by_issue_type {error}')
raise
def get_tickets_created_by_range(self):
response_data = {}
self.logger.debug("Inside get_tickets_created_by_range")
query_sql = """
WITH t AS(
SELECT
count(*),
to_char(created_date , 'YYYY-MM-DD') created_date
FROM
ttickets
WHERE project_id=%s AND created_date >= now() - INTERVAL %s
GROUP BY
to_char(created_date , 'YYYY-MM-DD')
ORDER BY
2 )
SELECT json_agg(t)
FROM t ;
"""
interval_val = f'{self.last_n_days} days' if int(
self.last_n_days) > 1 else f'{self.last_n_days} day'
values = (
self.project_id,
interval_val,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_tickets_created_by_range Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_tickets_created_by_range {error}')
raise
def get_tickets_still_open_last_n_days(self):
response_data = {}
self.logger.debug("Inside get_tickets_still_open_last_n_days")
query_sql = """
WITH t AS(
SELECT
count(*),
to_char(created_date , 'YYYY-MM-DD') created_date
FROM
ttickets
WHERE created_date >= now() - INTERVAL %s
AND ISSUE_STATUS='Open'
AND project_id=%s
GROUP BY
to_char(created_date , 'YYYY-MM-DD')
ORDER BY
2 )
SELECT json_agg(t)
FROM t ;
"""
values = (
f'{self.last_n_days} days',
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_tickets_still_open_last_n_days Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_tickets_still_open_last_n_days {error}')
raise
def get_tickets_open_by_module_last_n_days(self):
response_data = {}
self.logger.debug("Inside get_tickets_open_by_module_last_n_days")
query_sql = """
WITH t AS(
SELECT module,count(*)
FROM ttickets
WHERE created_date >= now() - INTERVAL %s
AND issue_status='Open'
AND project_id=%s
GROUP BY module
ORDER BY 2 desc
)
SELECT json_agg(t)
FROM t ;
"""
values = (
f'{self.last_n_days} days',
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_tickets_open_by_module_last_n_days Success with {row_count} row(s) Data{json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_tickets_open_by_module_last_n_days {error}'
)
raise
def get_count_of_tickets_by_channel_last_n_days(self):
response_data = {}
self.logger.debug("Inside get_count_of_tickets_by_channel_last_n_days")
query_sql = """
WITH t AS(
SELECT channel,count(*)
FROM ttickets
WHERE created_date >= now() - INTERVAL %s
AND project_id=%s
GROUP BY channel
ORDER BY 2 desc
)
SELECT json_agg(t)
FROM t ;
"""
values = (
f'{self.last_n_days} days',
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_count_of_tickets_by_channel_last_n_days Success with {row_count} row(s) data{json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
#self.jdb.close_conn()
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_count_of_tickets_by_channel_last_n_days {error}'
)
raise
class JirigoChatBot(object):
def __init__(self, data=None):
self.jdb = JirigoDBConn()
self.logger = Logger()
self.query = ""
self.project_abbr = data.get('project_abbr', None)
self.chatObj = Chat(pairs, reflections)
self.lemmer = WordNetLemmatizer()
self.punctuations = dict(
(ord(punctuation), None) for punctuation in string.punctuation)
self.tag_map = defaultdict(lambda: wn.NOUN)
self.tag_map['J'] = wn.ADJ
self.tag_map['V'] = wn.VERB
self.tag_map['R'] = wn.ADV
def perform_lemmatization(self, tokens):
return [self.lemmer.lemmatize(token) for token in tokens]
def get_processed_text(self, document):
return perform_lemmatization(
nltk.word_tokenize(
document.lower().translate(punctuation_removal)))
def get_query_response(self, query):
response_data = {}
self.query = query
item_no = ""
chatbot_res = ""
print('*' * 40)
print(query)
print(sent_tokenize(query))
print(word_tokenize(query))
print(string.punctuation)
word_tokens = word_tokenize(query)
lmtzr = WordNetLemmatizer()
# print(self.perform_lemmatization(pos_tag(word_tokenize(query))))
print(pos_tag(word_tokenize(query)))
print('*' * 40)
print(self.project_abbr)
if (re.match(r'^GET_ITEM_DETAILS',
self.chatObj.respond(self.query),
flags=re.IGNORECASE)):
for token, tag in pos_tag(word_tokens):
lemma = lmtzr.lemmatize(token, self.tag_map[tag[0]])
print(f'{self.project_abbr}:{lemma}')
if (re.search(self.project_abbr, lemma, re.IGNORECASE)):
self.item_no = lemma
break
chatbot_res = self.get_item_status()
else:
chatbot_res = self.chatObj.respond(self.query)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = chatbot_res
print(response_data)
return response_data
def bot_converse(self):
return self.chatObj.converse()
def get_canned_response(self):
return self.chatObj.respond(self.query)
def get_item_status(self):
response_data = {}
self.logger.debug(" Inside get_item_status")
print("Inside get_item_status")
print(self.item_no)
query_sql = """
SELECT item_no||' '||summary||' is in '||issue_status||' status' as item_status
FROM v_all_tickets_tasks vatt
WHERE lower(item_no)=lower(%s)
"""
values = (self.item_no, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
data = cursor.fetchone()
if data is None:
print('data is none')
data = f'Sorry, {self.item_no} doesn\'t exists'
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) get_item_status {data}'
)
return data[0]
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_item_status {error}')
raise
class JirigoMenus(object):
def __init__(self,data={}):
print("Initializing JirigoMenus")
self.project_id=data.get('project_id','')
self.project_name=data.get('project_name','')
self.role_id=data.get('role_id','')
self.menu_id=data.get('menu_id','')
self.new_menu_items=data.get('new_menu_items','')
self.workflow_id=data.get('workflow_id','')
self.role_name=data.get('role_name','')
self.is_active=data.get('is_active','')
self.project_name=data.get('project_name','')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by=data.get('modified_by')
self.modified_date=datetime.datetime.now()
self.menu_name=data.get('menu_name','')
self.menu_url=data.get('menu_url','')
self.parent_menu_id=data.get('parent_menu_id','')
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_all_menus_details_for_projectrole(self):
response_data={}
self.logger.debug("Inside get_all_menus_details_for_projectrole")
query_sql="""
WITH t AS (
SELECT
*
FROM
v_role_menu_details vrm
WHERE
vrm.role_id =%s
AND vrm.project_id =%s
)
SELECT json_agg(t) from t
"""
values=(self.role_id,self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_menus_details_for_projectrole {error}')
raise
def get_all_unassigned_menus_for_projectrole(self):
response_data={}
self.logger.debug("Inside get_all_unassigned_menus_for_projectrole")
query_sql="""
WITH t AS (
SELECT
*
FROM
v_menu_details vmd
WHERE
menu_id NOT IN (
SELECT
menu_id
FROM
v_role_menu_details vrmd
WHERE
project_id = %s
AND role_id = %s)
AND menu_id <> 1
)
SELECT json_agg(t) from t
"""
values=(self.project_id,self.role_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_unassigned_menus_for_projectrole {error}')
raise
def get_all_assigned_menus_for_projectrole(self):
response_data={}
self.logger.debug("Inside get_all_assigned_menus_for_projectrole")
query_sql="""
WITH t AS (
SELECT
*
FROM
v_menu_details vmd
WHERE
menu_id IN (
SELECT
menu_id
FROM
v_role_menu_details vrmd
WHERE
project_id = %s
AND role_id = %s)
AND menu_id <> 1
)
SELECT json_agg(t) from t
"""
values=(self.project_id,self.role_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_assigned_menus_for_projectrole {error}')
raise
def add_menus_to_role(self):
response_data={}
self.logger.debug("add_menus_to_role")
delete_all_menus_for_role="""
DELETE
FROM trole_menus
WHERE role_id=%s
"""
role_values=(self.role_id,)
# role_values=(self.role_name,self.role_status,self.start_date,self.end_date,self.modified_by,self.modified_date,self.role_id,)
print(role_values)
print("======role_values========")
print(f'role_values {role_values} at time {self.created_date}')
self.logger.debug(f'role_values {role_values} at time {self.created_date}')
add_menus_for_role="""
INSERT INTO trole_menus (role_id,menu_id,created_by,created_date)
VALUES %s
"""
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(delete_all_menus_for_role,role_values)
row_count=cursor.rowcount
self.logger.debug(f'Delete Success with {row_count} row(s) ID {self.role_id}')
print(f'new_menu_items {self.new_menu_items}')
menu_vals=[(self.role_id,x['menu_id'],self.created_by,self.created_date) for x in self.new_menu_items]
print(f'menu_vals {menu_vals}')
execute_values(cursor,add_menus_for_role,menu_vals,template="(%s,%s,%s,%s)")
row_count=cursor.rowcount
self.logger.debug(f'Bulk Insert Success with {row_count} row(s) ID {self.role_id} at time {self.created_date}')
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"roleId":self.role_id,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While add_menus_to_role {error}')
raise
def get_all_menus_for_project(self):
response_data={}
self.logger.debug("Inside get_all_menus_for_project")
query_sql="""
WITH t AS (
SELECT
menu_id,parent_menu_id ,menu_url,COALESCE (PATH,'root') path
FROM
v_menu_details vrm
ORDER BY path
)
SELECT json_agg(t) from t
"""
# values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} ')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While get_all_menus_for_project {error}')
raise
def add_menu(self):
response_data={}
self.logger.debug("add_menu")
add_menu_sql="""
INSERT INTO
TMENUS (menu_id,menu_name,menu_url,parent_menu_id,created_date,created_by)
VALUES((SELECT MAX(menu_id)+1 FROM tmenus),%s,%s,%s,%s,%s)
"""
add_menu_values=(self.menu_name,self.menu_name,self.parent_menu_id,self.created_date,self.created_by,)
print(f'{add_menu_sql} : {add_menu_values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(add_menu_sql,add_menu_values)
row_count=cursor.rowcount
self.logger.debug(f'Insert Success with {row_count} row(s) ID {self.menu_name} ')
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"manuName":self.menu_name,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While add_menu {error}')
raise
class JirigoTicket(object):
def __init__(self,data={}):
print("Initializing JirigoTicket")
print(f'In for Create/Update Ticket **** :{data}')
self.ticket_int_id = data.get('ticket_int_id')
self.jdb=JirigoDBConn()
self.summary = data.get('summary','')
self.description = data.get('description','')
self.severity = data.get('severity','')
self.priority = data.get('priority','')
self.issue_type = data.get('issue_type','')
self.issue_status = data.get('issue_status','')
self.is_blocking = data.get('is_blocking','N')
self.environment = data.get('environment','')
self.channel = data.get('channel','')
self.created_by = data.get('created_by','')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by','')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by','')
self.reported_date = data.get('reported_date',datetime.datetime.now())
self.ticket_no=data.get('ticket_no','-')
self.project_name=data.get('project_name','')
self.project_id=data.get('project_id','')
self.assignee_name=data.get('assignee_name','')
self.module=data.get('module','')
self.assignee_id = data.get('assignee_id','')
self.row_hash = data.get('row_hash','')
self.logger=Logger()
@classmethod
def for_create_update_ticket(cls,data):
print("-"*40)
print(f'In for_create_update_ticket Ticket :{data}')
print("-"*40)
return cls(data)
def create_ticket(self):
response_data={}
self.logger.debug("Inside Create Ticket")
insert_sql=""" INSERT INTO TTICKETS(ticket_no,summary,description,severity,priority,
issue_status,issue_type,environment,is_blocking,created_by,
created_date,reported_by,reported_date,assignee_id,project_id,module,channel)
VALUES (get_issue_no_by_proj(%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
get_user_id(%s),%s,get_user_id(%s),get_proj_id(%s),%s,%s) returning ticket_no;
"""
values=(self.project_name,self.summary,self.description,self.severity,self.priority,
"Open",self.issue_type,self.environment,self.is_blocking,self.created_by,
datetime.datetime.today(),self.reported_by,datetime.datetime.today(),
self.assignee_name,self.project_name,self.module,self.channel,)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_sql,values)
ticket_int_id=cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count=cursor.rowcount
self.logger.debug(f'Insert Success with {row_count} row(s) Ticket ID {ticket_int_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"ticket_no":ticket_int_id,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Creating Ticket {error}')
raise
def get_all_tickets(self):
response_data={}
self.logger.debug("Inside get_all_tickets")
query_sql="""
WITH t AS (
SELECT ticket_int_id,
ticket_no,
summary,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
channel,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date,
get_user_name(modified_by) modified_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') modified_date,
get_user_name(reported_by) reported_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') reported_date,
get_user_name(assignee_id) assigned_to
FROM ttickets
WHERE
project_id=COALESCE(%s,project_id) AND
(
created_by=COALESCE(%s,created_by) AND
COALESCE(assignee_id,-1)=COALESCE(%s,COALESCE(assignee_id,-1)) AND
COALESCE(modified_by,-1)=COALESCE(%s,COALESCE(modified_by,-1))
)
order by ticket_int_id
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
self.assignee_id = None if self.assignee_id == '' else self.assignee_id
self.created_by = None if self.created_by == '' else self.created_by
self.modified_by = None if self.modified_by == '' else self.modified_by
values=(self.project_id,self.created_by,self.assignee_id,self.modified_by,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) Ticket List {json_data}')
if json_data == None:
response_data['dbQryStatus']='Failure No Rows'
response_data['dbQryResponse']={}
else:
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Getting All Tickets {error}')
raise
def get_ticket_details(self):
self.logger.debug('Inside get_ticket_details')
response_data={}
query_sql="""
WITH t AS
(SELECT ticket_int_id,
ticket_no,
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
channel,
get_proj_name(project_id) project_name,
get_user_name(COALESCE(assignee_id, 0)) assignee_name,
get_user_name(COALESCE(created_by, 0)) created_by,
created_date,
get_user_name(COALESCE(modified_by, 0)) modified_by,
modified_date,
get_user_name(COALESCE(reported_by, 0)) reported_by,
reported_date,
row_hash
FROM ttickets
WHERE TICKET_NO=%s )
SELECT json_agg(t)
FROM t;
"""
values=(self.ticket_no,)
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
print(f'Select Success with {row_count} row(s) Ticket ID {json_data}')
self.logger.debug(f'Select Success with {row_count} row(s) Ticket ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if(self.jdb.dbConn):
print(f'Error While Creating Ticket :{error.pgcode} == {error.pgerror}')
print('-'*80)
raise
def update_ticket(self):
response_data={}
self.logger.debug("Inside Update Ticket update_tickets")
update_sql="""
UPDATE TTICKETS
SET summary=%s,
description=%s,
severity=COALESCE(%s,severity),
priority=COALESCE(%s,priority),
issue_status=COALESCE(%s,issue_status),
issue_type=COALESCE(%s,issue_type),
environment=COALESCE(%s,issue_type),
modified_by=%s,
modified_date=%s,
reported_by=COALESCE(get_user_id(%s),reported_by),
reported_date=%s,
project_id=get_proj_id(%s),
assignee_id=get_user_id(%s),
is_blocking=%s,
module=%s,
channel=%s
WHERE ticket_no=%s and row_hash=%s
"""
values=(self.summary,self.description,self.severity,self.priority,
self.issue_status,self.issue_type,self.environment,self.modified_by,
datetime.datetime.today(),self.reported_by,datetime.datetime.today(),
self.project_name,self.assignee_name,self.is_blocking,self.module,self.channel,
self.ticket_no,self.row_hash,)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(update_sql,values)
response_data['dbQryResponse']={"ticketId":self.ticket_no,"rowCount":cursor.rowcount}
self.jdb.dbConn.commit()
if response_data['dbQryResponse']['rowCount'] == 0:
response_data['dbQryStatus']='FailureNoRowFound'
else:
response_data['dbQryStatus']='Success'
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Updating Ticket {error}')
raise
def clone_ticket(self):
response_data={}
new_ticket_no='Error'
self.logger.debug("Inside Update Ticket update_tickets")
insert_sql="""
INSERT INTO ttickets (ticket_no, SUMMARY, description, issue_status, issue_type,
severity, priority, environment, is_blocking, module,created_by,
created_date, reported_by, reported_date, project_id,channel,row_hash)
SELECT get_issue_no_by_proj(get_proj_name(project_id)),
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
%s,
%s,
reported_by,
reported_date,
project_id,
channel,
row_hash
FROM ttickets
WHERE ticket_no=%s
returning ticket_no;
"""
values=(self.created_by,datetime.datetime.today(),self.ticket_no,)
self.logger.debug(f'Update : {insert_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_sql,values)
new_ticket_no=cursor.fetchone()[0]
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"clonedTicketNo":new_ticket_no,"rowCount":1}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While clone_ticket Ticket {error}')
raise
def update_ticket_assignee(self):
response_data={}
self.logger.debug("Inside update_ticket_assignee")
update_sql="""
UPDATE TTICKETS
SET assignee_id=%s,
modified_by=%s,
modified_date=%s
WHERE ticket_no=%s;
"""
values=(self.assignee_id,self.modified_by,self.modified_date,self.ticket_no,)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(update_sql,values)
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"taskNo":self.ticket_no,"rowCount":1}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While update_ticket_assignee {error}')
raise
class JirigoRefMaster(object):
def __init__(self,data={}):
self.project_id=data.get('project_id',0)
self.project_name=data.get('project_name',0)
self.ref_category=data.get('ref_category',None)
self.ref_name=data.get('ref_name',None)
self.ref_value=data.get('ref_value',None)
self.ref_id=data.get('ref_id',None)
self.order_id=data.get('order_id',None)
self.is_active=data.get('is_active',None)
self.created_by=data.get('created_by',1)
self.created_date = datetime.datetime.now()
self.modified_by=data.get('modified_by',1)
self.modified_date = datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_status_values(self):
response_data={}
self.logger.debug("Inside get_status_values")
query_sql="""
WITH t AS (
select
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and ref_name = 'Status'
and project_id=%s
order by order_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Select Reference Status {error}')
raise
def get_priority_values(self):
response_data={}
self.logger.debug("Inside get_priority_values")
query_sql="""
WITH t AS (
select
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and ref_name = 'Priority'
and project_id=%s
order by order_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Priority Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Select Reference Priority {error}')
raise
def get_severity_values(self):
response_data={}
self.logger.debug("Inside get_severity_values")
query_sql="""
WITH t AS (
select
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and ref_name = 'Severity'
and project_id=%s
order by order_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Severity Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Select Reference Severity {error}')
raise
def get_issue_type_values(self):
response_data={}
self.logger.debug("Inside get_issue_type_values")
query_sql="""
WITH t AS (
select
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and ref_name = 'Issue Type'
and project_id=%s
order by order_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Issue Type Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Select Reference Issue Type {error}')
raise
def get_module_values(self):
response_data={}
self.logger.debug("Inside get_module_values")
query_sql="""
WITH t AS (
select
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and ref_name = 'Module'
and project_id = %s
order by order_id
)
SELECT json_agg(t) from t;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Module Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Select Reference Module {error}')
raise
def get_all_ticket_refs(self):
response_data={}
self.logger.debug("Inside get_all_ticket_refs")
query_sql="""
with t as (
select
t.ref_name , json_agg( json_build_object( 'name', t.ref_value) ) refs
from
(
select
case when REF_NAME='Environment' then 'Environments'
when REF_NAME='Issue Type' then 'IssueTypes'
when REF_NAME='Priority' then 'Priorities'
when REF_NAME='Severity' then 'Severities'
when REF_NAME='Status' then 'IssueStatuses'
when REF_NAME='Module' then 'Modules'
when REF_NAME='Channel' then 'Channels'
else REF_NAME
end REF_NAME ,
ref_value
from
tref_master
where
ref_category = 'TICKETS'
and is_active = 'Y'
and project_id=%s
order by
ref_name,
order_id )t
group by
t.ref_name
)
select json_object_agg(t.ref_name,t.refs) from t ;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'get_all_ticket_refs Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_ticket_refs {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_ticket_refs {error}')
raise
def get_all_task_refs(self):
response_data={}
self.logger.debug("Inside get_all_task_refs")
query_sql="""
with t as (
select
t.ref_name , json_agg( json_build_object( 'name', t.ref_value) ) refs
from
(
select
case when REF_NAME='Environment' then 'Environments'
when REF_NAME='Issue Type' then 'IssueTypes'
when REF_NAME='Priority' then 'Priorities'
when REF_NAME='Severity' then 'Severities'
when REF_NAME='Status' then 'IssueStatuses'
when REF_NAME='Module' then 'Modules'
else REF_NAME
end REF_NAME ,
ref_value
from
tref_master
where
ref_category = 'TASKS'
and is_active = 'Y'
and project_id=%s
order by
ref_name,
order_id )t
group by
t.ref_name
)
select json_object_agg(t.ref_name,t.refs) from t ;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
# print("*"*56)
# print(json_data)
# print("*"*56)
row_count=cursor.rowcount
self.logger.debug(f'get_all_task_refs Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_task_refs {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_task_refs {error}')
raise
def get_all_sprint_refs(self):
response_data={}
self.logger.debug("Inside get_all_sprint_refs")
query_sql="""
with t as (
select
t.ref_name , json_agg( json_build_object( 'name', t.ref_value) ) refs
from
(
select
case when REF_NAME='Environment' then 'Environments'
when REF_NAME='Issue Type' then 'IssueTypes'
when REF_NAME='Priority' then 'Priorities'
when REF_NAME='Severity' then 'Severities'
when REF_NAME='Status' then 'SprintStatuses'
when REF_NAME='Module' then 'Modules'
end REF_NAME ,
ref_value
from
tref_master
where
ref_category = 'SPRINT'
and is_active = 'Y'
and project_id=%s
order by
ref_name,
order_id )t
group by
t.ref_name
)
select json_object_agg(t.ref_name,t.refs) from t ;
"""
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
print(json_data)
row_count=cursor.rowcount
self.logger.debug(f'get_all_sprint_refs Select Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
print(response_data)
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select Reference get_all_sprint_refs {error}')
if(self.jdb.dbConn):
print(f'Error While Select Reference get_all_sprint_refs {error}')
raise
def get_all_refs_for_show_and_editing(self):
response_data={}
self.logger.debug("Inside get_all_refs_for_editing")
query_sql="""
SELECT
json_agg(t2)
FROM
(
SELECT
json_agg(t1) all_refs
FROM
(
SELECT
get_proj_name(project_id) project_name,
ref_category,
ref_name,
ref_id,
ref_value,
project_id,
is_active,
order_id
FROM
tref_master
ORDER BY
1,
2,
3
) t1
GROUP BY
ref_category,
ref_name
) t2
"""
# query_sql="""
# with t as (
# SELECT
# get_proj_name(project_id) project_name,
# ref_category,
# ref_name,
# ref_id,
# ref_value,
# project_id,
# is_active,
# order_id
# FROM
# tref_master
# ORDER BY
# 1,
# 2,
# 3)
# select json_agg(t) FROM t
# """
# self.ref_category = None if self.ref_category == '' else self.ref_category
# self.ref_name = None if self.ref_name == '' else self.ref_name
# self.project_id = None if self.project_id == '' else self.project_id
values=(self.project_id,self.ref_category,self.ref_name,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_refs_for_editing Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=[json_data]
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error In get_all_refs_for_editing {error}')
raise
def get_all_reference_categories(self):
response_data={}
self.logger.debug("Inside get_all_refs_for_editing")
query_sql="""
WITH t AS (
SELECT
ref_category
FROM
tref_master
WHERE project_id=coalesce(%s,project_id)
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
values=(self.project_id,self.ref_category,self.ref_name,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_refs_for_editing Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error In get_all_refs_for_editing {error}')
raise
def insert_reference(self):
response_data={}
self.logger.debug("insert_reference")
#
# self.order_id will be "0" for auto assignment, where max +1 of subset
# of ref_name and rev_category will be used.
#
insert_refs_sql="""
INSERT
INTO tref_master
(ref_category,ref_name,ref_value,
is_active,created_by,created_date,
order_id,
project_id)
VALUES (
%s,%s,%s,%s,%s,%s,
(
SELECT CASE WHEN %s=0 THEN COALESCE(max(order_id), 0)+ 1
ELSE %s
END
FROM tref_master
WHERE ref_category=%s
AND ref_name=%s
AND project_id=get_proj_id(%s)
),
get_proj_id(%s)
) returning ref_id;
"""
values=( self.ref_category,self.ref_name,self.ref_value,
self.is_active,self.created_by,self.created_date,
self.order_id,self.order_id,
self.ref_category,self.ref_name,self.project_name,
self.project_name,
)
print(f' {insert_refs_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_refs_sql,values)
ref_id=cursor.fetchone()[0]
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Insert Success with {row_count} row(s) REF ID {ref_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"Refs":ref_id,"No Of Rows Inserted":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Updating insert_reference {error}')
raise
def update_reference(self):
response_data={}
self.logger.debug("update_reference")
update_refs_sql="""
UPDATE tref_master
set ref_value=%s,
modified_by=%s,
modified_date=%s
WHERE ref_id=%s;
"""
values=( self.ref_value,self.modified_by,self.modified_date,self.ref_id,)
print(f' {update_refs_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(update_refs_sql,values)
row_count=cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(f'Insert Success with {row_count} row(s) REF ID {self.ref_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"Refs":self.ref_id,"No Of Rows Updated":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While Updating update_reference {error}')
raise
def get_all_task_ticket_link_references(self):
response_data={}
self.logger.debug("Inside get_all_task_ticket_link_references")
query_sql="""
WITH t AS (
SELECT
ref_id,ref_category,ref_name,ref_value
FROM
tref_master
WHERE project_id=coalesce(%s,project_id)
AND ref_category='GENERAL'
AND ref_name='Task Ticket Links'
AND is_active='Y'
ORDER BY order_id
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
values=(self.project_id,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'get_all_task_ticket_link_references Success with {row_count} row(s) data {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error In get_all_task_ticket_link_references {error}')
raise
class JirigoHomePage(object):
def __init__(self, data={}):
print("Initializing JirigoMenus")
self.project_id = data.get('project_id', '')
self.project_name = data.get('project_name', '')
self.role_id = data.get('role_id', '')
self.menu_id = data.get('menu_id', '')
self.new_menu_items = data.get('new_menu_items', '')
self.workflow_id = data.get('workflow_id', '')
self.role_name = data.get('role_name', '')
self.is_active = data.get('is_active', '')
self.project_name = data.get('project_name', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.current_user_id = data.get('current_user_id', 0)
self.num_rows = data.get('num_rows', 10)
self.jdb = JirigoDBConn()
self.logger = Logger()
def select_query_as_json(self, query_name, query_sql, params):
self.logger.debug('select_query_as_json')
response_data = {}
try:
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, params)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(f'{query_name} {row_count} row(s) T{json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Ticket :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise
def get_recent_proj_ticket_task_activities(self):
self.logger.debug('Inside get_ticket_task_activities_timeline')
response_data = {}
query_sql = """
WITH t AS
(
WITH t_project AS (
SELECT
'PROJECT' AS p_or_u, order_no, item_no, summary, item_type, description, issue_status, issue_type, severity, priority, environment, is_blocking, get_user_name(created_by) created_by, created_date, get_user_name(modified_by) modified_by, modified_date, get_user_name(reported_by) reported_by, reported_date, project_id, assignee_id, module_name,
CASE
WHEN modified_date IS NULL THEN 'C'
WHEN modified_date > created_date THEN 'M'
ELSE 'C'
END AS c_or_m
FROM
v_all_tickets_tasks vatt
WHERE
project_id = %s
ORDER BY
CASE
WHEN modified_date IS NULL THEN created_date
WHEN modified_date > created_date THEN modified_date
ELSE modified_date
END DESC
LIMIT %s),
t_user AS (
SELECT
'USER', order_no, item_no, summary, item_type, description, issue_status, issue_type, severity, priority, environment, is_blocking, get_user_name(created_by) created_by, created_date, get_user_name(modified_by) modified_by, modified_date, get_user_name(reported_by) reported_by, reported_date, project_id, assignee_id, module_name,
CASE
WHEN modified_date IS NULL THEN 'C'
WHEN modified_date > created_date THEN 'M'
ELSE 'C'
END AS c_or_m
FROM
v_all_tickets_tasks vatt
WHERE
project_id =%s
AND (
(created_by = %s AND modified_by IS NULL )
OR modified_by =%s
)
ORDER BY
CASE
WHEN modified_date IS NULL THEN created_date
WHEN modified_date > created_date THEN modified_date
ELSE modified_date
END DESC
LIMIT %s)
SELECT
*
FROM
t_project
UNION ALL
SELECT
*
FROM
t_user
)
SELECT json_agg(t)
FROM t;
"""
values = (
self.project_id,
self.num_rows,
self.project_id,
self.current_user_id,
self.current_user_id,
self.num_rows,
)
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
response_data = self.select_query_as_json(
'get_ticket_task_activities_timeline', query_sql, values)
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Ticket :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise
class JirigoProjectWorkflow(object):
def __init__(self, data={}):
print("Initializing JirigoSprints")
self.workflow_name = data.get('workflow_name', '')
self.workflow_id = data.get('workflow_id', 0)
self.project_id = data.get('project_id', '')
self.role_id = data.get('role_id', '')
self.current_status = data.get('current_status', '')
self.ref_category = data.get('ref_category', '')
self.project_name = data.get('project_name', '')
self.workflow_type = data.get('workflow_type', '')
self.next_allowed_statuses = data.get('next_allowed_statuses', '')
self.step_full_details = data.get('step_full_details', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_next_allowed_workflow_statuses(self):
response_data = {}
self.logger.debug("Inside get_next_allowed_workflow_statuses")
query_sql = """
WITH t AS (
SELECT
tpwm.workflow_id,
jsonb_array_elements(tpws.next_allowed_statuses ) ->> 'status' status,
jsonb_array_elements(tpws.next_allowed_statuses ) ->> 'nextStatuses' next_allowed_statuses
FROM
tproj_workflow_master tpwm,
tproj_role_workflow tprw,
tproj_workflow_steps tpws
WHERE
tpwm.project_id = tprw.project_id
AND tpwm.workflow_id = tprw.workflow_id
AND tprw.workflow_id = tpws.workflow_id
AND tpwm.project_id =%s
AND tprw.role_id =%s
)
SELECT next_allowed_statuses
FROM t
WHERE status=%s
"""
values = (
self.project_id,
self.role_id,
self.current_status,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Stes {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = eval(json_data)
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_next_allowed_workflow_statuses {error}')
raise
def get_workflow_details_for_update(self):
response_data = {}
self.logger.debug("Inside get_workflow_details_for_update")
query_sql = """
WITH t AS (
SELECT
tws.step_full_details
FROM
tproj_workflow_master tpwm,
tproj_role_workflow tprw,
tproj_workflow_steps tws
WHERE
tpwm.project_id = tprw.project_id
AND tpwm.project_id =%s
AND tprw.workflow_id =%s
AND tws.workflow_id =tprw.workflow_id
AND tprw.role_id =%s
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.workflow_id,
self.role_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
pprint(json_data)
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_workflow_details_for_update {error}')
raise
def create_project_workflow(self):
response_data = {}
self.logger.debug("create_workflow ")
create_workflow_sql = """INSERT
INTO tproj_workflow_master
(project_id, workflow_name,
workflow_type, created_by, created_date)
VALUES (%s,%s,
%s,%s,%s)
RETURNING workflow_id;
"""
create_workflow_steps_sql = """
INSERT
INTO tproj_workflow_steps
(
workflow_id,next_allowed_statuses,step_full_details,
created_by,created_date
)
VALUES (
%s,%s,%s,
%s,%s
)
"""
values_create_workflow = (
self.project_id,
self.workflow_name,
self.workflow_type,
self.created_by,
self.created_date,
)
self.logger.debug(
f'{create_workflow_sql} values {values_create_workflow}')
try:
print('#' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(create_workflow_sql, values_create_workflow)
ret_workflow_id = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) Worflow Id {ret_workflow_id}'
)
print('@' * 80)
values_create_workflow_steps = (
ret_workflow_id,
Json(self.next_allowed_statuses),
Json(self.step_full_details),
self.created_by,
self.created_date,
)
self.logger.debug(
f'{create_workflow_steps_sql} values {values_create_workflow_steps}'
)
cursor.execute(create_workflow_steps_sql,
values_create_workflow_steps)
self.jdb.dbConn.commit()
self.logger.debug(
f'Insert Success with {row_count} row(s) Worflow Id {ret_workflow_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"ret_workflow_id": ret_workflow_id,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Project Workflow Tasks {error}')
raise
def get_all_statuses_for_workflow(self):
response_data = {}
self.logger.debug("Inside get_all_statuses_for_workflow")
query_sql = """
WITH t AS (
SELECT ref_id ,ref_value,order_id
FROM tref_master tm
WHERE ref_category =%s AND REF_NAME='Status'
AND project_id=%s
ORDER BY order_id
)
SELECT json_agg(t) from t;
"""
values = (
self.ref_category,
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_statuses_for_workflow {error}')
raise
def get_workflows_not_assigned_to_project_role(self):
response_data = {}
self.logger.debug("Inside get_workflows_for_project")
query_sql = """
WITH t AS (
SELECT *
FROM tproj_workflow_master
WHERE project_id=%s
and workflow_id NOT IN (
SELECT workflow_id
from tproj_role_workflow
where role_id=%s
and project_id=%s
)
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.role_id,
self.project_id,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Ticket ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_workflows_not_assigned_to_project_role {error}'
)
raise
def get_project_role_workflow_list_for_update(self):
response_data = {}
self.logger.debug("Inside get_project_role_workflow_list_for_update")
query_sql = """
WITH t AS (
SELECT
tr.role_id,
tr.role_name,
tr.is_active,
tpr.project_id ,
get_proj_name(tpr.project_id) project_name,
tprw.workflow_id ,
get_workflow_name (tprw.workflow_id ) workflow_name
FROM troles tr
INNER JOIN tproject_roles tpr ON
tr.role_id = tpr.role_id
LEFT OUTER JOIN tproj_role_workflow tprw ON
tr.role_id = tprw.role_id
WHERE
tr.is_active = 'Y'
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql} ')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While get_project_role_workflow_list_for_update {error}'
)
raise
def update_project_workflow(self):
response_data = {}
self.logger.debug("create_workflow ")
update_workflow_steps_sql = """
UPDATE tproj_workflow_steps
SET
next_allowed_statuses=%s,
step_full_details=%s,
modified_by=%s,
modified_date=%s
WHERE workflow_id=%s
"""
try:
print('@' * 80)
values_update_workflow_steps = (
Json(self.next_allowed_statuses),
Json(self.step_full_details),
self.modified_by,
self.modified_date,
self.workflow_id,
)
self.logger.debug(
f'{update_workflow_steps_sql} values {values_update_workflow_steps}'
)
cursor = self.jdb.dbConn.cursor()
row_count = cursor.rowcount
cursor.execute(update_workflow_steps_sql,
values_update_workflow_steps)
self.jdb.dbConn.commit()
self.logger.debug(
f'Update Success with {row_count} row(s) Worflow Id {self.workflow_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"ret_workflow_id": self.workflow_id,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Project Workflow Tasks {error}')
raise