def get_manager_mainpage():
userinfo = session.get('profile')
if userinfo['role']!= 'Project Manager':
abort(401)
manager_email = userinfo['email']
user_id = userinfo['user_id']
#Get notifications
notification_list = NotificationController.get_notifications(userinfo['user_id'])
notification_count = len(notification_list)
project_list = Project.query.join(Map_user_proj, Project.p_id == Map_user_proj.p_id)\
.join(Users, Users.user_id == Map_user_proj.user_id)\
.add_columns(Project.p_id,Project.p_name,Project.p_desc,Project.p_start_date,Project.p_end_date)\
.filter(Users.user_email == manager_email).all()
project = [Project.json_format(proj) for proj in project_list]
data = {
"userinfo" : userinfo,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"page" : "manager_mainpage",
"notification" : notification_list,
"notification_count" : notification_count,
"project" : project
}
return render_template('manager-mainpage.html', data=data)
def get_project_details(project_id):
userinfo = session.get('profile')
#Check if the request is valid
authorized_personnel = Map_user_proj.query.with_entities(Map_user_proj.user_id).filter(Map_user_proj.p_id == project_id)\
.filter(Map_user_proj.user_id ==userinfo['user_id']).all()
if len(authorized_personnel) == 0 and userinfo['role'] != 'Admin':
abort(401)
project_list = Project.query.get(project_id)
project = Project.json_format(project_list)
project_users_list = Map_user_proj.query.join(Users, Map_user_proj.user_id == Users.user_id)\
.add_columns(Map_user_proj.p_id, Map_user_proj.user_id, Map_user_proj.user_role, Users.user_name, Users.user_email)\
.filter(Map_user_proj.p_id == project_id).all()
project_users = [project_user_list_to_json(row) for row in project_users_list]
for u in project_users:
valid_delete = AdminController.is_valid_remove(u['user_id'])
u['valid_delete'] = valid_delete
project_tickets_list = Ticket.query.join(Users, Ticket.assigned_user_id == Users.user_id, isouter=True )\
.add_columns(Ticket.t_id,Ticket.t_title,Ticket.t_desc, Users.user_name.label('assigned_user_id'), \
Ticket.submitter_email, Ticket.p_id, Ticket.t_priority, Ticket.t_status, Ticket.t_type,\
Ticket.t_create_date, Ticket.t_close_date)\
.filter(Ticket.p_id == project_id).all()
project_tickets = [Ticket.json_format(row) for row in project_tickets_list]
#user_list = Users.query.filter(Users.user_role != "Admin").all()
sql = text("""select user_id, user_name,user_email
,user_pwd,user_role,update_date from users
where user_role!='Admin' and user_id not in (select user_id from map_user_proj where p_id ="""+str(project_id)+""")""")
result = db.session.execute(sql)
users = [row for row in result]
user_list_json = [Users.json_format(u) for u in users]
#Get notifications
notification_list = NotificationController.get_notifications(userinfo['user_id'])
notification_count = len(notification_list)
data = {
"project" :project,
"project_users" : project_users,
"ticket" : project_tickets,
"userinfo" : userinfo,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"page" : "project_detail",
"userlist" : user_list_json,
"notification" : notification_list,
"notification_count" : notification_count
}
return render_template('project_details.html', data = data )
def get_user_history(user_id):
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
userentry = Users.query.get(user_id)
user = Users.json_format(userentry)
user_project_list = Project.query.join(Map_user_proj, Project.p_id == Map_user_proj.p_id)\
.add_columns(Project.p_id, Project.p_name)\
.filter(Map_user_proj.user_id == user_id)
sql = text("""select p_id,p_name,p_desc,p_start_date, p_end_date from project where p_id
not in (select p_id from map_user_proj where user_id ="""+str(user_id)+""")""")
result = db.session.execute(sql)
project_name_list = [row for row in result]
project_name_list_json = [Project.json_format(p) for p in project_name_list]
ticket_list =""
user_role = user['role']
user_email = user['email']
#For developer, get list of assigned tickets
if user_role == "Developer":
ticket_list = Ticket.query.join(Project, Ticket.p_id == Project.p_id)\
.add_columns(Ticket.t_id, Ticket.t_title, Ticket.t_status,Project.p_name)\
.filter(Ticket.assigned_user_id == user_id)
#For user, get list of submitted tickets
elif user_role == "User":
ticket_list = Ticket.query.join(Project, Ticket.p_id == Project.p_id)\
.add_columns(Ticket.t_id, Ticket.t_title, Ticket.t_status, Project.p_name)\
.filter(Ticket.submitter_email == user_email)
#For manager, get list of submitted tickets
elif user_role == "Manager":
ticket_list = Ticket.query.join(Project, Ticket.p_id == Project.p_id)\
.add_columns(Ticket.t_id, Ticket.t_title, Ticket.t_status, Project.p_name)\
.filter(Ticket.submitter_email == user_email)
delete_is_valid = is_valid_remove(user_id)
data = {
"project" : project_name_list_json,
"userinfo" : userinfo,
"user_project" : user_project_list,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"user" : [user],
"page": "user-details",
"ticket": ticket_list,
"is_valid_remove":delete_is_valid
}
return render_template('user_details.html', data = data)
def create_user():
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
project_name_list = Project.query.all()
project_name_list_json = [Project.json_format(row) for row in project_name_list]
data = {
"project" : project_name_list_json,
"userinfo" : userinfo,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"page": "create-user"
}
return render_template('userform.html', data = data)
def admin_get_projects():
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
project_list = Project.query.all()
project= [Project.json_format(row) for row in project_list]
print(project)
data = {
"project" : project,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"page" : "projects",
"userinfo" : userinfo,
}
return render_template('admin-mainpage.html', data = data)
def get_ticket_info(ticket_id):
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
ticket_json = Ticket.query.get(ticket_id)
ticket = Ticket.json_format(ticket_json)
project = Project.query.get(ticket_json.p_id)
project_name_list = Project.query.all()
project_name_list_json = [Project.json_format(row) for row in project_name_list]
priority_array = constants.PRIORITY
status_array = constants.STATUS
data = {
"project" : project_name_list_json,
"userinfo" : userinfo,
"user_project" : project,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"ticket": ticket,
"page": "edit-ticket",
"priority_array": priority_array,
"status_array":status_array
}
return render_template('ticketform.html', data = data)
def get_user_info(user_id):
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
user_entry = Users.query.get(user_id)
user = Users.json_format(user_entry)
project = Project.query.join(Map_user_proj, Project.p_id == Map_user_proj.p_id)\
.add_columns(Project.p_name, Project.p_id)\
.filter(Map_user_proj.user_id == user_id)
project_name_list = Project.query.all()
project_name_list_json = [Project.json_format(row) for row in project_name_list]
data = {
"project" : project_name_list_json,
"userinfo" : userinfo,
"user_project" : project,
"role" : userinfo['role'],
"username" : userinfo['nickname'],
"user": user,
"page": "edit-user"
}
return render_template('userform.html', data = data)
def submit_project():
userinfo = session.get('profile')
if userinfo['role']!= 'Admin':
abort(401)
proj_name = request.form.get('proj_name')
proj_desc = request.form.get('proj_desc')
today = date.today()
#p_create_date = today.strftime("%d/%m/%Y")
p_create_date = constants.CURRENT_DATE
p_id = db.session.query(func.max(Project.p_id)).all()
if p_id[0][0] == None:
proj_entry = Project(1, proj_name, proj_desc, p_create_date, "")
else:
proj_entry = Project(p_id[0][0] + 1, proj_name, proj_desc, p_create_date, "")
try:
proj_entry.insert()
except:
print(sys.exc_info())
abort(500)
return redirect("/admin/projects")
def get_project_chart(project_id):
userinfo = session.get('profile')
if userinfo['role']!= 'Project Manager':
abort(401)
user_id = userinfo['user_id']
mth_id = datetime.now().month
yr = datetime.now().year
if project_id==0:
query = text(""" SELECT mapid.mth_name AS mth_name,
mapid.p_id AS p_id,
COALESCE(filter.cnt, 0) AS cnt
FROM (SELECT config.mth_name,
config.mth_id,
config.year,
proj.p_id
FROM (select * from month_config where id <= (select id from month_config where mth_id = """+str(mth_id)+ """ and year= """ +str(yr)+""") and id >= (select id - 11 from month_config where mth_id = """+str(mth_id)+ """ and year= """ +str(yr)+""")) config
CROSS JOIN (SELECT p_id
FROM map_user_proj
WHERE user_id = """ +str(user_id)+"""
AND user_role = 'Project Manager') proj) mapid
LEFT OUTER JOIN (SELECT Count(filter.t_id) AS cnt,
filter.p_id,
Date_part('month', filter.date) AS month ,
Date_part('year', filter.date) AS yr
FROM (SELECT tick.t_id,
tick.p_id,
To_date(tick.t_create_date, 'DD/MM/YYYY')
AS
date
FROM ticket tick
WHERE p_id IN (SELECT p_id
FROM map_user_proj
WHERE
user_id = """ +str(user_id)+"""
AND user_role =
'Project Manager')) filter
GROUP BY filter.p_id,
Date_part('month', filter.date),
Date_part('year', filter.date)) filter
ON mapid.mth_id = filter.month
AND mapid.year = filter.yr
AND mapid.p_id = filter.p_id """)
result = db.session.execute(query)
chart_data = [Month_config.json_format(row) for row in result]
project_list_query = text("""SELECT proj.p_id AS p_id,
proj.p_name AS p_name
FROM project proj
INNER JOIN map_user_proj map
ON proj.p_id = map.p_id
WHERE map.user_id ="""+str(user_id)+"""
AND map.user_role = 'Project Manager' """)
result = db.session.execute(project_list_query)
project_list = [Project.project_list_json_format(row) for row in result]
else:
query = text(""" SELECT mapid.mth_name AS mth_name,
mapid.p_id AS p_id,
COALESCE(filter.cnt, 0) AS cnt
FROM (SELECT config.mth_name,
config.mth_id,
config.year,
proj.p_id
FROM (select * from month_config where id <= (select id from month_config where mth_id = """+str(mth_id)+ """ and year= """ +str(yr)+""") and id >= (select id - 11 from month_config where mth_id = """+str(mth_id)+ """ and year= """ +str(yr)+""")) config
CROSS JOIN (SELECT """ +str(project_id)+""" as p_id) proj) mapid
LEFT OUTER JOIN (SELECT Count(filter.t_id) AS cnt,
filter.p_id,
Date_part('month', filter.date) AS month ,
Date_part('year', filter.date) AS yr
FROM (SELECT tick.t_id,
tick.p_id,
To_date(tick.t_create_date, 'DD/MM/YYYY')
AS
date
FROM ticket tick
WHERE p_id ="""+str(project_id)+ """) filter
GROUP BY filter.p_id,
Date_part('month', filter.date),
Date_part('year', filter.date)) filter
ON mapid.mth_id = filter.month
AND mapid.year = filter.yr
AND mapid.p_id = filter.p_id """)
result = db.session.execute(query)
chart_data = [Month_config.json_format(row) for row in result]
project_list_query = text("""SELECT proj.p_id AS p_id,
proj.p_name AS p_name
FROM project proj
INNER JOIN map_user_proj map
ON proj.p_id = map.p_id
WHERE map.user_id ="""+str(user_id)+"""
AND map.p_id = """+str(project_id)+ """
AND map.user_role = 'Project Manager' """)
result = db.session.execute(project_list_query)
project_list = [Project.project_list_json_format(row) for row in result]
data = {
"chart_data": chart_data,
"project_list": project_list
}
return data