def get_project_assigned_tickets():
#Authoraize the Developer
userInfo = session.get('userProfile', 'not set')
if userInfo['role'] != 'dev':
abort(401)
# Fetch the tickets with same assigned developer id as the Developer
ticket = Ticket.query.join(Project, Project.p_id == Ticket.p_id)\
.join(Users, Users.users_id==Ticket.users_id)\
.add_columns(Ticket.t_id.label('id'),Users.users_name.label('user_name'),Ticket.submitter_email.label('email'),\
Ticket.t_title.label('title'),Ticket.t_desc.label('desc'),Ticket.t_priority.label('priority'),\
Ticket.t_type.label('type'),Ticket.t_status.label('status'),Ticket.t_create_date.label('create_date'),\
Ticket.t_close_date.label('close_date'),Project.p_name.label('p_id'))\
.filter(Ticket.users_id==userInfo['id']).all()
data = {
'ticket': ticket,
'user_name': userInfo['name'],
'role': userInfo['role'],
'page': 'tickets',
'notify': notification.notify(userInfo['id']),
'tickets_type': 'assigned',
'nav_bar': 'assignedtickets'
}
return render_template('list.html', data=data)
def error_404(error):
userInfo = session.get('userProfile', 'not set')
data = {
'user_name': userInfo['name'],
'role': userInfo['role'],
'page': 'error',
'error': '404',
'message': error,
'notify': notification.notify(userInfo['id']),
}
return render_template('error.html', data=data)
def get_dev_project():
userInfo = session.get('userProfile', 'not set')
dev_email = userInfo['email']
if userInfo['role'] != 'dev' and userInfo['role'] != 'manager':
abort(401)
#fetch list of projects that the Developer is assigned to using the map table
#join Project, map_users_proj on project id
#join map_users_proj, users on user_id
#The map_users_proj table contains the maping of project to user
project = Project.query.join(Map_users_proj, Map_users_proj.p_id == Project.p_id)\
.join(Users, Users.users_id == Map_users_proj.users_id).filter(Users.users_email == dev_email).all()
project = [Project.format(pro) for pro in project]
data = {
'project': project,
'user_name': userInfo['name'],
'role': userInfo['role'],
'notify': notification.notify(userInfo['id']),
'page': 'projects'
}
return render_template('list.html', data=data)
def get_ticket_details(ticket_id):
userInfo = session.get('userProfile')
# Fetch ticket record from tickets table
ticket = Ticket.query.join(Project, Project.p_id == Ticket.p_id)\
.join(Users, Users.users_id==Ticket.users_id)\
.add_columns(Ticket.t_id.label('id'),Ticket.users_id.label('user_id'),Users.users_name.label('user_name'),\
Ticket.submitter_email.label('email'),Ticket.t_title.label('title'),Ticket.t_desc.label('desc'),\
Ticket.t_priority.label('priority'),Ticket.t_type.label('type'),Ticket.t_status.label('status'),\
Ticket.t_create_date.label('create_date'),Ticket.t_close_date.label('close_date'),\
Project.p_name.label('p_id'),Ticket.p_id.label('project_id'))\
.filter(Ticket.t_id==ticket_id).all()
project_assigned = Map_users_proj.query.filter(Map_users_proj.users_id==userInfo['id'])\
.filter(Map_users_proj.p_id==ticket[0][13]).all()
if ticket[0][4] != userInfo['email'] and not project_assigned and userInfo[
'role'] != 'admin':
abort(401)
if not ticket:
abort(404)
# Fetch all the history record for this specific ticket from the ticket_history table
#Join ticket_history, users on id to fetch user names
detail = Ticket_history.query.join(Users,Ticket_history.users_id == Users.users_id,isouter=True)\
.add_columns(Ticket_history.t_id,Ticket_history.t_status,Ticket_history.t_update_date, Ticket_history.priority,Users.users_name.label('users_id'))\
.filter(Ticket_history.t_id == ticket_id)
detail = [Ticket_history.format(row) for row in detail]
# Fetch all the commnets for this specific ticket from the comments table
#Join comment, users on id to fetch user names.
comment = Comment.query.join(Users, Comment.users_id==Users.users_id)\
.add_columns(Comment.t_id, Comment.comment, Comment.date, Users.users_name.label('users_id'))\
.filter(Comment.t_id==ticket_id)\
.order_by(Comment.c_id.desc()).all()
comment = [Comment.format(co) for co in comment]
#Fetch all the people in the project
#join map_users_proj, Ticket on project id
#join map_users_proj, Users on user id
#Function is used to assign a user to specific ticket
project_user = Ticket.query.join(Map_users_proj, Ticket.p_id == Map_users_proj.p_id)\
.join(Users, Users.users_id == Map_users_proj.users_id)\
.add_columns(Users.users_id, Users.users_name)\
.filter(Ticket.t_id == ticket_id).filter(Users.users_role == 'dev').all()
data = {
'ticket': ticket,
'detail': detail,
'role': userInfo['role'],
'id': userInfo['id'],
'user_name': userInfo['name'],
'comment': comment,
'page': 'ticket_detail',
'notify': notify(userInfo['id']),
'project_user': project_user,
'status': STATUS
}
return render_template('ticket-detail.html', data=data)
def get_project_details(project_id):
userInfo = session.get('userProfile')
project_assigned = Map_users_proj.query.filter(Map_users_proj.users_id==userInfo['id'])\
.filter(Map_users_proj.p_id==project_id).all()
if not project_assigned and userInfo['role'] != 'admin':
abort(401)
# Fetch all the tickets of the project with project_id
ticket = Ticket.query.join(Users, Users.users_id==Ticket.users_id)\
.add_columns(Ticket.t_id.label('id'),Ticket.users_id.label('user_id'),Users.users_name.label('user_name'),\
Ticket.submitter_email.label('email'),Ticket.t_title.label('title'),Ticket.t_desc.label('desc'),\
Ticket.t_priority.label('priority'),Ticket.t_type.label('type'),Ticket.t_status.label('status'),\
Ticket.t_create_date.label('create_date'),Ticket.t_close_date.label('close_date'))\
.filter(Ticket.p_id==project_id).all()
# Fetch the users info assigned to the project
# join Users, map_users_proj on user_id
# join map_users_proj, Project on project_id
# join is used to map the users to project table using the mapping table map_users_proj
user = Users.query.join(Map_users_proj, Map_users_proj.users_id == Users.users_id)\
.join(Project, Project.p_id == Map_users_proj.p_id)\
.add_columns(Users.users_id.label('id'),Users.users_name.label('name'),\
Users.users_email.label('email'),Map_users_proj.users_role.label('role'))\
.filter(Project.p_id == project_id).all()
user = [Users.short_format(u) for u in user]
for u in user:
delete_user = False
if u['id'] > 21 or userInfo['id'] == 1:
delete_user = True
u['delete_user'] = delete_user
# Fetch the project records with primary key project_id
project = Project.query.get(project_id)
# Only authorized by the admin
# fetch all the users lists
# this is used to add users to project/change role of specific user
all_users = ""
if userInfo['role'] == "admin":
# all_users = Users.query.filter(Users.users_role!='admin').all()
# all_users = [all.format() for all in all_users]
sql = text("""select users_id , users_name ,users_email
,users_password,users_role,update_date from users
where users_role!='admin' and users_id not in
(select users_id from map_users_proj where p_id =""" +
str(project_id) + """)""")
all_users = db.session.execute(sql)
all_users = [Users.format(all) for all in all_users]
data = {
'users': user,
'ticket': ticket,
'project': project.format(),
'all_users': all_users,
'role': userInfo['role'],
'user_name': userInfo['name'],
'notify': notification.notify(userInfo['id']),
'page': 'project_detail'
}
return render_template('project-detail.html', data=data)