def get_user_information(userIndex):
subQ = dao.query(User).filter(User.userIndex == userIndex).subquery()
return dao.query(subQ.c.userId, subQ.c.nickName, subQ.c.eMail, UserSetting.languageIndex, UserSetting.userIndex,
UserSetting.thema, UserSetting.comment, UserSetting.isOpen).\
join(UserSetting,
subQ.c.userIndex == UserSetting.userIndex)
def select_problem(problemIndex=None, problemName=None):
if problemName:
return dao.query(Problem). \
filter(and_(Problem.problemName == problemName,
Problem.isDelete == False))
return dao.query(Problem).\
filter(and_(Problem.problemIndex == problemIndex if problemIndex
else Problem.problemIndex != problemIndex,
Problem.isDelete == False))
def update_userSetting(userIndex, languageIndex=None, thema=None, comment=None, isOpen=None):
return dao.query(UserSetting).\
filter(UserSetting.userIndex==userIndex).\
update(dict(languageIndex=languageIndex if languageIndex else UserSetting.languageIndex,
thema=thema if thema else UserSetting.thema,
comment=comment if comment else UserSetting.comment,
isOpen=UserSetting.isOpen if isOpen is None else isOpen))
def select_recent_code(problemIndex, userIndex=None):
codeListSubquery = select_code(problemIndex=problemIndex,
isCompile=True).subquery()
if userIndex:
temp = dao.query(func.max(codeListSubquery.c.codeIndex).label('leastIndex'), codeListSubquery.c.userIndex,
codeListSubquery.c.problemIndex, codeListSubquery.c.isOpen,
func.max(codeListSubquery.c.date).label('least')). \
group_by(codeListSubquery.c.userIndex, codeListSubquery.c.problemIndex).subquery()
return dao.query(temp).filter(temp.c.userIndex == userIndex)
else:
return dao.query(func.max(codeListSubquery.c.codeIndex).label('leastIndex'), codeListSubquery.c.userIndex,
codeListSubquery.c.problemIndex, func.max(codeListSubquery.c.date).label('least')).\
group_by(codeListSubquery.c.userIndex, codeListSubquery.c.problemIndex)
def select_code(userIndex=None,
problemIndex=None,
languageIndex=None,
isCompile=None,
codeIndex=None):
if codeIndex:
return dao.query(Code).\
filter(Code.codeIndex == codeIndex)
return dao.query(Code).\
filter(and_(Code.userIndex == userIndex if userIndex
else Code.userIndex != userIndex,
Code.problemIndex == problemIndex if problemIndex
else Code.problemIndex != problemIndex,
Code.languageIndex == languageIndex if languageIndex
else Code.languageIndex != languageIndex,
Code.isCompile == isCompile if isCompile is not None
else Code.isCompile != isCompile))
def select_user(userIndex=None, userId=None, nickName=None, authority=None):
return dao.query(User).\
filter(and_(User.userIndex == userIndex if userIndex
else User.userIndex != userIndex,
User.userId == userId if userId
else User.userId != userId,
User.nickName == nickName if nickName
else User.nickName != nickName,
User.authority == authority if authority
else User.authority != authority))
def main():
try:
problems = select_problem().subquery()
topProblems = get_topProblem().subquery()
topProblems = dao.query(problems, topProblems).\
join(topProblems,
topProblems.c.problemIndex == problems.c.problemIndex).\
order_by(topProblems.c.submitCount.desc()).all()
except AttributeError as e:
print e
topProblems = []
try:
users = select_user().subquery()
topUsers = get_total_score_each_users().subquery()
topUsers = dao.query(users, topUsers).\
join(topUsers,
topUsers.c.userIndex == users.c.userIndex).\
order_by(topUsers.c.totalScore.desc()).all()
except AttributeError as e:
print e
topUsers = []
try:
user = select_user(userIndex=session['userIndex'])
return render_template(
'main.html',
topUsers=topUsers[:3] if len(topUsers) > 3 else topUsers,
topProblems=topProblems[:3]
if len(topProblems) > 3 else topProblems,
user=user)
except Exception as e:
return render_template(
'main.html',
topUsers=topUsers[:3] if len(topUsers) > 3 else topUsers,
topProblems=topProblems[:3]
if len(topProblems) > 3 else topProblems,
user=None)
def select_board_article(boardIndex=None):
boardSubquery = dao.query(Board, User.userId, User.nickName).\
join(Board,
User.userIndex == Board.userIndex).\
filter(and_(Board.boardIndex == boardIndex if boardIndex
else Board.boardIndex != boardIndex,
Board.isDelete == False)).\
subquery()
replySubquery = dao.query(ReplyOfBoard, User.userId, User.nickName).\
join(ReplyOfBoard,
User.userId == ReplyOfBoard.userIndex).\
filter(and_(ReplyOfBoard.boardIndex == boardIndex if boardIndex
else ReplyOfBoard.boardIndex != boardIndex,
ReplyOfBoard.isDelete == False)).\
subquery()
return dao.query(boardSubquery, replySubquery.c.replyOfBoardIndex, replySubquery.c.userIndex.label('replyUserIndex'),
replySubquery.c.content.label('replyContent')).\
join(boardSubquery,
replySubquery.c.boardIndex == boardSubquery.c.boardIndex)
def update_user(userIndex,
password=None,
nickName=None,
eMail=None,
lastMatchDate=None,
authority=None):
return dao.query(User).\
filter(User.userIndex == userIndex).\
update(dict(password = password if password else User.password,
nickName = nickName if nickName else User.nickName,
eMail = eMail if eMail else User.eMail,
lastMatchDate = lastMatchDate if lastMatchDate else User.lastMatchDate,
authority = authority if authority else User.authority))
def viewCode(codeIndex):
tempCode = select_code(codeIndex=codeIndex).subquery()
temp = select_language().subquery()
code = dao.query(temp.c.language, tempCode).\
join(tempCode,
tempCode.c.languageIndex == temp.c.languageIndex).first()
userInfo = select_userInformationInProblem(
userIndex=session['userIndex']).first()
problem = select_problem(problemIndex=code.problemIndex).first()
user = select_user(userIndex=session['userIndex']).first()
return render_template('viewCode.html',
userInfo=userInfo,
code=code,
problem=problem,
user=user)
def problem(problemIndex):
thema = [
'chrome', 'clouds', 'eclipse', 'github', 'monokai', 'textmate',
'tomorrow'
]
try:
langQuery = select_language()
userIndex = session['userIndex']
problemData = select_problem(problemIndex=problemIndex).first()
userInfoSub = select_userSetting(userIndex=userIndex).subquery()
temp = langQuery.subquery()
userInfo = dao.query(temp.c.language, userInfoSub).\
join(userInfoSub, userInfoSub.c.languageIndex == temp.c.languageIndex).first()
language = langQuery.all()
if userInfo is None:
try:
dao.add(insert_userSetting(userIndex=userIndex))
dao.commit()
redirect(url_for('.problem'))
except Exception as e:
redirect(url_for('.problem'))
return render_template('problem.html',
problemData=problemData,
userInfo=userInfo,
thema=thema,
language=language)
except Exception as e:
print e
flash('다시 시도해주세요.')
return redirect(url_for('.main'))
def update_like_count(boardIndex, value):
return dao.query(Board).\
filter(Board.boardIndex == boardIndex).\
update(dict(likeCount = Board.likeCount + value))
def select_notice(userIndex):
return dao.query(Notice).\
filter(Notice.userIndex == userIndex)
def update_reply_delete(boardIndex, isDelete=True):
dao.query(Board).\
filter(Board.boardIndex==boardIndex).\
update(dict(isDelete=isDelete))
def update_reply_modify(boardIndex, content):
dao.query(Board).\
filter(Board.boardIndex==boardIndex).\
update(dict(content=content))
def update_notice_read(noticeIndex, userIndex):
return dao.query(Notice).\
filter(and_(Notice.noticeIndex == noticeIndex,
Notice.userIndex == userIndex)).\
update(dict(isRead = True))
def select_scriptOfProblem(problemIndex):
return dao.query(ScriptOfProblem).\
filter(ScriptOfProblem.problemIndex==problemIndex)
def update_code(codeIndex, date=None, isCompile=None, isOpen=None):
return dao.query(Code).\
filter(Code.codeIndex == codeIndex).\
update(dict(date=date if date else Code.date,
isCompile=isCompile if isCompile is not None else Code.isCompile,
isOpen=isOpen if isOpen is not None else Code.isOpen))
def update_board_modify(boardIndex, problemIndex, title, content):
dao.query(Board).\
filter(Board.boardIndex==boardIndex).\
update(dict(problemIndex=problemIndex,
title=title,
content=content))
def replayMyList(isChallenge):
try:
user = select_user(userIndex=session['userIndex']).subquery()
problem = select_problem().subquery()
totalUser = select_user().subquery()
if isChallenge:
# if user is challenger
replayData = select_dataOfMatch(
challengerIndex=session['userIndex']).subquery()
joinedquery = dao.query(user.c.nickName.label('challengerNickName'), user.c.userId.label('challengerId'),
replayData.c.dataOfMatchIndex, replayData.c.problemIndex,
replayData.c.challengerIndex, replayData.c.championIndex,
replayData.c.result).\
join(replayData,
replayData.c.challengerIndex == user.c.userIndex).\
subquery()
userReplayData = dao.query(totalUser.c.nickName.label('championNickName'),
totalUser.c.userId.label('championId'),
joinedquery).\
join(joinedquery,
joinedquery.c.championIndex == totalUser.c.userIndex).\
subquery()
else:
# if user is champion
replayData = select_dataOfMatch(
championIndex=session['userIndex']).subquery()
joinedquery = dao.query(user.c.nickName.label('championNickName'), user.c.userId.label('championId'),
replayData.c.dataOfMatchIndex, replayData.c.problemIndex,
replayData.c.challengerIndex, replayData.c.championIndex, replayData.c.result). \
join(replayData,
replayData.c.championIndex == user.c.userIndex).\
subquery()
userReplayData = dao.query(totalUser.c.nickName.label('challengerNickName'),
totalUser.c.userId.label('challengerId'), joinedquery).\
join(joinedquery,
joinedquery.c.challengerIndex == totalUser.c.userIndex).\
subquery()
userReplayData = dao.query(problem.c.problemName, userReplayData).\
join(userReplayData,
userReplayData.c.problemIndex == problem.c.problemIndex).all()
return render_template('replaymylist.html',
userReplayData=userReplayData[::-1],
isChallenge=isChallenge)
except AttributeError as e:
print e
return render_template('replaymylist.html',
userReplayData=[],
isChallenge=isChallenge)
except Exception as e:
print e
flash('다시 시도해주세요.')
return redirect(url_for('.main'))
def select_userSetting(userIndex):
return dao.query(UserSetting).\
filter(UserSetting.userIndex==userIndex)
