def get(self):
args = self.parser.parse_args()
query = 'select * from (' \
'select c.* from card c, category cat, boardContains bc, categorizedAs ca where ' \
'bc.board_title = %s and ' \
'bc.category_id = cat.id and ' \
'cat.id = ca.category_id and ' \
'ca.card_id = c.id ' \
'UNION ' \
'select c.* from card c, isbackloggedon ibo where ' \
'ibo.board_title = %s and ' \
'ibo.card_id = c.id' \
') all_cards ' \
'where ' \
'1 = 1'
if args['priority']:
query += ' and all_cards.priority = \'%s\'' % args['priority']
if args['past_due']:
query += ' and all_cards.due_date < now()'
query += ';'
cur.execute(query, [cln(args['board_title']), cln(args['board_title'])])
cards = cur.fetchall()
# Add assignedTo field
for c in cards:
query = 'select tm.name, tm.email from assignedTo at, TeamMember tm where ' \
'at.member_email = tm.email and ' \
'at.card_id = %s;'
cur.execute(query, [c['id']])
c['assignedto'] = cur.fetchone()
if args['assignee']:
cards = [c for c in cards if c['assignedto']] # Filter by exists
cards = [c for c in cards if c['assignedto']['email'] == cln(args['assignee'])] # Filter by assignee
return jsonify(cards)
def post(self):
args = self.parser.parse_args()
# Ensure board exists
cur.execute('select * from board where title = %s;', [cln(args['board_title'])])
if not cur.fetchone():
return {'error': 'Board does not exist'}
query = 'insert into category (title, description) values (%s, %s) returning id, title, description;'
cur.execute(query, [cln(args['cat_title']), cln(args['cat_desc'])])
cat = cur.fetchone()
query = 'insert into boardContains (board_title, category_id) values (%s, %s);'
cur.execute(query, [cln(args['board_title']), cat['id']])
return jsonify(cat)
def put(self, team_name, email):
# Make sure teammember and team exist
cur.execute('select * from team where name = %s;', [cln(team_name)])
if not cur.fetchone():
return {'error': 'Team does not exist.'}
cur.execute('select * from teammember where email = %s;', [cln(email)])
if not cur.fetchone():
return {'error': 'TeamMember does not exist.'}
# Add the composedOf relation
query = 'insert into composedOf (team_name, member_email) values (%s, %s);'
cur.execute(query, [cln(team_name), cln(email)])
return True
def get(self, title):
title_clean = cln(title)
cur.execute('select * from board where title = %s;',
[title_clean])
board = cur.fetchone()
query = '''select c.id, c.title, c.description
from boardContains bc, Category c
where bc.board_title = %s and
bc.category_id = c.id;'''
cur.execute(query, [title_clean])
categories = cur.fetchall()
for c in categories:
query = '''select *
from categorizedAs ca, Card c
where ca.category_id = %s and
ca.category_id = c.id;'''
cur.execute(query, [c['id']])
cards = cur.fetchall()
c['cards'] = cards
board['categories'] = categories
# TODO: return backlog
return jsonify(board)
def put(self, email, card_id):
# Check user and card exist
query = 'select * from teammember where email = %s;'
cur.execute(query, [cln(email)])
tm = cur.fetchone()
query = 'select * from card where id = %s;'
cur.execute(query, [card_id])
if not tm and not cur.fetchone():
return {'error': 'Card and/or TeamMember does/do not exist.'}
# Delete previous assignments to that card
query = 'delete from assignedTo where card_id = %s;'
cur.execute(query, [card_id])
# Create new assignedTo
query = 'insert into assignedTo(member_email, card_id) values (%s, %s);'
cur.execute(query, [cln(email), card_id])
# conn.commmit()
return True
def post(self, name, email, role, pwd, ent_type):
if cln(ent_type).lower() not in ['seniordev', 'intern', 'juniordev']:
return {'error': 'Invalid type. Must be seniordev, intern, or juniordev.'}
query = "insert into teammember(name, email, role, password, hiredate) values (%s, %s, %s, %s, now());"
cur.execute(query, [cln(name), cln(email), cln(role), cln(pwd)])
query = "insert into {} (email) values (%s);".format(ent_type)
cur.execute(query, [cln(email)])
# conn.commit()
return True
def post(self, board_title, priority, description, title, due_date):
# Make sure board exists and priority is right
query = 'select * from board where title = %s;'
cur.execute(query, [cln(board_title)])
if not cur.fetchone() and cln(priority) in ['LOW', 'MED', 'HIGH']:
return {'error': 'Board does not exist.'}
query = 'insert into card (due_date, priority, description, title) values (%s, %s, %s, %s) ' \
'returning id, due_date, priority, description, title;'
cur.execute(query, [cln(due_date), cln(priority), cln(description), cln(title)])
card = cur.fetchone()
card_id = card['id']
query = 'insert into isbackloggedon (board_title, card_id) values (%s, %s);'
cur.execute(query, [cln(board_title), card_id])
# conn.commit()
return jsonify(card)
def post(self, team_name, board_title, board_desc):
# Make sure Team exists
cur.execute('select * from team where name = %s;', [cln(team_name)])
if not cur.fetchone():
return {'error': 'Team does not exist'}
# Make sure Board DNE
cur.execute('select * from board where title = %s;', [cln(board_title)])
if cur.fetchone():
return {'error': 'Board already exists.'}
# Create the Board and runBy
query = 'insert into Board (title, description) values (%s, %s);'
cur.execute(query, [cln(board_title), cln(board_desc)])
query = 'insert into runBy (board_title, team_name) values (%s, %s);'
cur.execute(query, [cln(board_title), cln(team_name)])
return True
def post(self, name, email):
query = 'select * from teammember tm, seniordev sd where tm.email = %s and sd.email = %s;'
cur.execute(query, [cln(email), cln(email)])
teammember = cur.fetchone()
if not teammember:
return {'error': 'No team member with that email.'}
# Create team
query = '''insert into Team(name) values (%s);'''
cur.execute(query, [cln(name)])
# Create composedOf
query = "insert into composedOf(team_name, member_email) values (%s, %s);"
cur.execute(query, [cln(name), cln(email)])
# Create hasLeader
query = "insert into hasLeader(team_name, dev_email) values (%s, %s);"
cur.execute(query, [cln(name), cln(email)])
# conn.commit()
return True
def get(self, email, pwd):
uemail = cln(email)
# Get pertinent user
query = "select name, email, role, hiredate from teammember where email = %s and password = %s;"
cur.execute(query, [uemail, cln(pwd)])
uinfo = cur.fetchone()
if not uinfo:
return {'error': 'User not found with those credentials.'}
# Get user's team
query = "select t.name from team t, composedOf co where t.name = co.team_name and co.member_email = %s;"
cur.execute(query, [uemail])
tinfo = cur.fetchone()
query = 'select tm.name, tm.email, tm.role, tm.hireDate from composedOf co, teammember tm ' \
'where co.team_name = %s and co.member_email = tm.email;'
cur.execute(query, [tinfo['name']])
tinfo['members'] = cur.fetchall()
# Get boards run by team
query = "select b.title, b.description from board b, runby rb " \
"where b.title = rb.board_title and rb.team_name = %s;"
cur.execute(query, [tinfo['name']])
board = cur.fetchone()
# Get Categories in board
btitle = board['title']
cur.execute('select * from board where title = %s;',
[btitle])
board = cur.fetchone()
query = 'select c.id, c.title, c.description ' \
'from boardContains bc, Category c ' \
'where bc.board_title = %s and bc.category_id = c.id;'
cur.execute(query, [btitle])
categories = cur.fetchall()
def get_assigned_to(cards):
for card in cards:
q = 'select member_email, name ' \
'from assignedTo, teammember ' \
'where card_id = %s and member_email = email;'
cur.execute(q, [card['id']])
cinfo = cur.fetchone()
if cinfo:
card['assignedto'] = cinfo
else:
card['assignedto'] = None
# Get Cards for each Category
for c in categories:
query = 'select c.id, c.due_date, c.priority, c.description, c.title ' \
'from categorizedAs ca, Card c ' \
'where ca.category_id = %s and ca.card_id = c.id;'
cur.execute(query, [c['id']])
cards = cur.fetchall()
get_assigned_to(cards)
c['cards'] = cards
board['categories'] = categories
# Get Cards in board backlog
query = 'select c.id, c.due_date, c.priority, c.description, c.title ' \
'from card c, isbackloggedon ibo ' \
'where ibo.board_title = %s and ibo.card_id = c.id;'
cur.execute(query, [btitle])
backlogged_cards = cur.fetchall()
get_assigned_to(backlogged_cards)
board['backlog'] = backlogged_cards
# Put it all together
tinfo['board'] = board
uinfo['team'] = tinfo
return jsonify(uinfo)
def get(self, email):
cur.execute('SELECT email, hiredate, name, role FROM TeamMember WHERE email = %s;',
[cln(email)])
tm = cur.fetchone()
return jsonify(tm)