def update_submission_code_view_count(lastSubmission, memberCourseProblemParameter = MemberCourseProblemParameter()):
dao.query(Submissions).\
filter(Submissions.memberId == memberCourseProblemParameter.memberId,
Submissions.courseId == memberCourseProblemParameter.courseId,
Submissions.problemId == memberCourseProblemParameter.problemId,
Submissions.submissionCount == lastSubmission.c.submissionCount).\
update(dict(viewCount = Submissions.viewCount + 1))
def select_sorted_articles(articlesOnBoard, filterFindParameter = None, articleType = ENUMResources().const.NOTICE, isAll = False):
if articleType == ENUMResources().const.NOTICE:
# All Notice get
if isAll:
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType == ENUMResources().const.NOTICE).\
subquery()
# Filter Case
if filterFindParameter\
and filterFindParameter.filterCondition\
and filterFindParameter.filterCondition != ' ':
articlesOnBoard = search_articles(articlesOnBoard,
filterFindParameter).subquery()
# 5 Get notice
else:
# Server written notice get 2
notice = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType == articleType).\
order_by(articlesOnBoard.c.updateDate.desc()).\
limit(OtherResources().const.VIEW_NOTICE)
return notice
else:
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.articleType != ENUMResources().const.NOTICE).\
subquery()
# Filter Case
if filterFindParameter\
and filterFindParameter.filterCondition\
and filterFindParameter.filterCondition != ' ':
articlesOnBoard = search_articles(articlesOnBoard,
filterFindParameter).subquery()
return dao.query(articlesOnBoard).\
order_by(articlesOnBoard.c.articleIndex.desc())
def update_recent_access_date(memberId):
from GradeServer.model.members import Members
''' doesnt need to add exception handler here? '''
dao.query(Members).\
filter(Members.memberId == memberId).\
update(dict(lastAccessDate = datetime.now()))
def update_code_view_reply_counting(submissionIndex,
VIEW_INCREASE=1,
REPLY_INCREASE=0):
dao.query(DataOfSubmissionBoard).\
filter(DataOfSubmissionBoard.submissionIndex == submissionIndex).\
update(dict(viewCount = DataOfSubmissionBoard.viewCount + VIEW_INCREASE,
submissionReplyCount = DataOfSubmissionBoard.submissionReplyCount + REPLY_INCREASE))
def insert_invitee_id(teamName, inviteeId):
# Update or Insert
# if not exist then Insert
if not dao.query(TeamInvitations).\
filter(TeamInvitations.teamName == teamName,
TeamInvitations.inviteeId == inviteeId).\
first():
newInvitee = TeamInvitations(teamName = teamName,
inviteeId = inviteeId)
dao.add(newInvitee)
# else then Update
else :
dao.query(TeamInvitations).\
filter(TeamInvitations.teamName == teamName,
TeamInvitations.inviteeId == inviteeId).\
update(dict(isDeleted = ENUMResources().const.FaLSE))
# Commit Exception
try:
dao.commit()
flash(inviteeId + get_message('inviteeSucceeded'))
except Exception:
dao.rollback()
return get_message('updateFailed')
return None
def select_solved_problems_counts(submissions):
submissions = dao.query(submissions).\
filter(submissions.c.status == ENUMResources().const.SOLVED).\
group_by(submissions.c.problemIndex).\
subquery()
return dao.query(func.count(submissions.c.memberId).label('sumOfSolvedProblemCount'))
def update_problem(problemIndex, problemDifficulty, solutionCheckType, limitedTime, limitedMemory):
dao.query(Problems).\
filter(Problems.problemIndex == problemIndex).\
update(dict(problemDifficulty = problemDifficulty,
solutionCheckType = solutionCheckType,
limitedTime = limitedTime,
limitedMemory = limitedMemory))
def select_accept_courses():
# 서버 마스터는 모든 과목에 대해서, 그 외에는 지정된 과목에 대해서
try:
# Server Master
if SETResources().const.SERVER_ADMINISTRATOR in session[SessionResources().const.AUTHORITY]:
myCourses = dao.query(RegisteredCourses.courseId,
RegisteredCourses.courseName,
RegisteredCourses.endDateOfCourse)
# Class Master
elif SETResources().const.COURSE_ADMINISTRATOR in session[SessionResources().const.AUTHORITY]:
myCourses = dao.query(RegisteredCourses.courseId,
RegisteredCourses.courseName,
RegisteredCourses.endDateOfCourse).\
filter(RegisteredCourses.courseAdministratorId == session[SessionResources().const.MEMBER_ID])
# User
else:
myCourses = dao.query(Registrations.courseId,
RegisteredCourses.courseName,
RegisteredCourses.endDateOfCourse).\
join(RegisteredCourses,
Registrations.courseId == RegisteredCourses.courseId).\
filter(Registrations.memberId == session[SessionResources().const.MEMBER_ID])
# Session Error Catch
except Exception:
myCourses = dao.query(RegisteredCourses.courseId,
RegisteredCourses.courseName,
RegisteredCourses.endDateOfCourse).\
filter(RegisteredCourses.courseId == None)
return myCourses
def update_view_reply_counting(articleIndex,
VIEW_INCREASE=1,
REPLY_INCREASE=0):
dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.articleIndex == articleIndex).\
update(dict(viewCount = ArticlesOnBoard.viewCount + VIEW_INCREASE,
replyCount = ArticlesOnBoard.replyCount + REPLY_INCREASE))
def select_ranks(submissions):
# # Total Submission Count (Rank Page Server Error Exception)
submissionCount = dao.query(submissions.c.memberIdIndex,
func.sum(submissions.c.solutionCheckCount).label('solutionCheckCount')).\
group_by(submissions.c.memberIdIndex).\
subquery()
# 중복 제거푼 문제숫
sumOfSolvedProblemCount = dao.query(submissions.c.memberIdIndex).\
join(Submissions,
and_(Submissions.status == ENUMResources().const.SOLVED,
Submissions.submissionIndex == submissions.c.submissionIndex)).\
group_by(Submissions.submissionIndex).\
subquery()
sumOfSolvedProblemCount = dao.query(sumOfSolvedProblemCount,
func.count(sumOfSolvedProblemCount.c.memberIdIndex).label('sumOfSolvedProblemCount')).\
group_by(sumOfSolvedProblemCount.c.memberIdIndex).\
subquery()
#SubmitCount and SolvedCount Join
return dao.query(submissionCount.c.memberIdIndex,
submissionCount.c.solutionCheckCount,
sumOfSolvedProblemCount.c.sumOfSolvedProblemCount,
(sumOfSolvedProblemCount.c.sumOfSolvedProblemCount / submissionCount.c.solutionCheckCount * 100).label('solvedRate')).\
join(sumOfSolvedProblemCount,
and_(submissionCount.c.memberIdIndex == sumOfSolvedProblemCount.c.memberIdIndex))
def update_code_is_like(submissionIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnSubmission).\
filter(and_(LikesOnSubmission.submissionIndex == submissionIndex,
LikesOnSubmission.codeLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
def select_ranks(submissions):
# # Total Submission Count (Rank Page Server Error Exception)
submissionCount = dao.query(submissions.c.memberIdIndex,
func.sum(submissions.c.solutionCheckCount).label('solutionCheckCount')).\
group_by(submissions.c.memberIdIndex).\
subquery()
# 중복 제거푼 문제숫
sumOfSolvedProblemCount = dao.query(submissions.c.memberIdIndex).\
join(Submissions,
and_(Submissions.status == ENUMResources().const.SOLVED,
Submissions.submissionIndex == submissions.c.submissionIndex)).\
group_by(Submissions.submissionIndex).\
subquery()
sumOfSolvedProblemCount = dao.query(sumOfSolvedProblemCount,
func.count(sumOfSolvedProblemCount.c.memberIdIndex).label('sumOfSolvedProblemCount')).\
group_by(sumOfSolvedProblemCount.c.memberIdIndex).\
subquery()
#SubmitCount and SolvedCount Join
return dao.query(submissionCount.c.memberIdIndex,
submissionCount.c.solutionCheckCount,
sumOfSolvedProblemCount.c.sumOfSolvedProblemCount,
(sumOfSolvedProblemCount.c.sumOfSolvedProblemCount / submissionCount.c.solutionCheckCount * 100).label('solvedRate')).\
join(sumOfSolvedProblemCount,
and_(submissionCount.c.memberIdIndex == sumOfSolvedProblemCount.c.memberIdIndex))
def members_sorted(members, sortCondition = LanguageResources().const.ID[1]):
# MEMBER ID, ORGANIZATION NAME, MEMBER NAME, LAST_ACCESS_DATE, END DATE 정렬
if sortCondition == LanguageResources().const.ID[1]:
memberRecords = dao.query(members).\
order_by(members.c.memberId.asc(),
members.c.memberName.asc(),
members.c.lastAccessDate.asc(),
members.c.limitedUseDate.asc())
# MEMBER NAME, ORGANIZATION NAME, MEMBER ID, LAST_ACCESS_DATE, END DATE 정렬
elif sortCondition == LanguageResources().const.Name[1]:
memberRecords = dao.query(members).\
order_by(members.c.memberName.asc(),
members.c.memberId.asc(),
members.c.lastAccessDate.asc(),
members.c.limitedUseDate.asc())
# LAST_ACCESS_DATE, ORGANIZATION NAME, MEMBER ID, MEMBER NAME, END DATE 정렬
elif sortCondition == LanguageResources().const.LastAccess[1]:
memberRecords = dao.query(members).\
order_by(members.c.lastAccessDate.asc(),
members.c.memberName.asc(),
members.c.memberId.asc(),
members.c.limitedUseDate.asc())
# END DATE, ORGANIZATION NAME, MEMBER ID, MEMBER NAME, LAST_ACCESS_DATE 정렬
elif sortCondition == LanguageResources().const.FinishDate[1]:
memberRecords = dao.query(members).\
order_by(members.c.limitedUseDate.asc(),
members.c.memberId.asc(),
members.c.memberName.asc(),
members.c.lastAccessDate.asc())
return memberRecords
def update_article_is_like(articleIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnBoard).\
filter(and_(LikesOnBoard.articleIndex == articleIndex,
LikesOnBoard.boardLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
def team_information(teamName, error = None):
"""
when you push a team name of team page
"""
try:
# 팀 정보
try:
teamInformation = dao.query(Teams).\
filter(Teams.teamName == teamName,
Teams.isDeleted == ENUMResources().const.FALSE).first()
except Exception:
# None Type Exception
teamInformation = []
# 팀 멤버 정보
try:
teamMemberRecords = dao.query(RegisteredTeamMembers.teamMemberId).\
filter(RegisteredTeamMembers.teamName == teamName,
RegisteredTeamMembers.isDeleted == ENUMResources().const.FALSE).\
order_by(RegisteredTeamMembers.isTeamMaster.asc(),
RegisteredTeamMembers.teamMemberId.asc()).all()
except Exception:
# None Type Exception
teamMemberRecords = []
return render_template(HTMLResources().const.TEAM_INFORMATION_HTML,
SETResources = SETResources,
SessionResources = SessionResources,
LanguageResources = LanguageResources,
teamInformation = teamInformation,
teamMemberRecords = teamMemberRecords)
except Exception:
# Unknown Error
return unknown_error()
def members_sorted(members, sortCondition=LanguageResources().const.ID[1]):
# MEMBER ID, ORGANIZATION NAME, MEMBER NAME, LAST_ACCESS_DATE, END DATE 정렬
if sortCondition == LanguageResources().const.ID[1]:
memberRecords = dao.query(members).\
order_by(members.c.memberId.asc(),
members.c.memberName.asc(),
members.c.lastAccessDate.asc(),
members.c.limitedUseDate.asc())
# MEMBER NAME, ORGANIZATION NAME, MEMBER ID, LAST_ACCESS_DATE, END DATE 정렬
elif sortCondition == LanguageResources().const.Name[1]:
memberRecords = dao.query(members).\
order_by(members.c.memberName.asc(),
members.c.memberId.asc(),
members.c.lastAccessDate.asc(),
members.c.limitedUseDate.asc())
# LAST_ACCESS_DATE, ORGANIZATION NAME, MEMBER ID, MEMBER NAME, END DATE 정렬
elif sortCondition == LanguageResources().const.LastAccess[1]:
memberRecords = dao.query(members).\
order_by(members.c.lastAccessDate.asc(),
members.c.memberName.asc(),
members.c.memberId.asc(),
members.c.limitedUseDate.asc())
# END DATE, ORGANIZATION NAME, MEMBER ID, MEMBER NAME, LAST_ACCESS_DATE 정렬
elif sortCondition == LanguageResources().const.FinishDate[1]:
memberRecords = dao.query(members).\
order_by(members.c.limitedUseDate.asc(),
members.c.memberId.asc(),
members.c.memberName.asc(),
members.c.lastAccessDate.asc())
return memberRecords
def select_solved_problems_counts(submissions):
submissions = dao.query(submissions).\
filter(submissions.c.status == ENUMResources().const.SOLVED).\
group_by(submissions.c.problemIndex).\
subquery()
return dao.query(
func.count(submissions.c.memberId).label('sumOfSolvedProblemCount'))
def update_replies_on_board_is_like(
boardReplyIndex,
memberIdIndex,
isLikeCancelled=ENUMResources().const.FALSE):
dao.query(LikesOnReplyOfBoard).\
filter(and_(LikesOnReplyOfBoard.boardReplyIndex == boardReplyIndex,
LikesOnReplyOfBoard.boardReplyLikerIdIndex == memberIdIndex)).\
update(dict(isLikeCancelled = isLikeCancelled))
def update_recent_access_date(memberIdIndex,
lastAceesDate,
isDeleted=ENUMResources().const.FALSE):
''' doesnt need to add exception handler here? '''
dao.query(Members).\
filter(and_(Members.memberIdIndex == memberIdIndex,
Members.isDeleted == isDeleted)).\
update(dict(lastAccessDate = lastAceesDate))
def update_problem(problemIndex, problemDifficulty, solutionCheckType,
limitedTime, limitedMemory):
dao.query(Problems).\
filter(Problems.problemIndex == problemIndex).\
update(dict(problemDifficulty = problemDifficulty,
solutionCheckType = solutionCheckType,
limitedTime = limitedTime,
limitedMemory = limitedMemory))
def select_solved_problems_counts(submissions):
submissions = (
dao.query(submissions)
.filter(submissions.c.status == ENUMResources().const.SOLVED)
.group_by(submissions.c.problemId, submissions.c.courseId)
.subquery()
)
return dao.query(func.count(submissions.c.memberId).label("sumOfSolvedProblemCount"))
def update_article_modify(articleIndex, problemIndex, articleType, articleParameter, isDeleted = ENUMResources().const.FALSE):
dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.articleIndex == articleIndex,
ArticlesOnBoard.isDeleted == isDeleted).\
update(dict(problemIndex = problemIndex,
articleType = articleType,
title = articleParameter.title,
content = articleParameter.content,
updateIp = articleParameter.updateIp,
updateDate = articleParameter.updateDate))
def has_duplicated_member(memberId):
try:
dao.query(Members).\
filter(Members.memberId == memberId).\
first().\
memberId
except:
return False
return True
def select_articles(activeTabCourseId, isDeleted = ENUMResources().const.FALSE):
# activate Tab Select
if activeTabCourseId == OtherResources().const.ALL:
return dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.isDeleted == isDeleted)
else:
return dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.isDeleted == isDeleted,
or_(ArticlesOnBoard.courseId == activeTabCourseId,
ArticlesOnBoard.courseId == None))
def problems_sorted(problems, sortCondition = LanguageResources().const.Name[1]):
if sortCondition == LanguageResources().const.Name[1]:
problemRecords = dao.query(problems).\
order_by(problems.c.problemName.asc(),
problems.c.problemDifficulty.asc())
# Difficulty ProblemName 정렬
elif sortCondition == LanguageResources().const.Difficulty[1]:
problemRecords = dao.query(problems).\
order_by(problems.c.problemDifficulty.asc(),
problems.c.problemName.asc())
return problemRecords
def problems_sorted(problems, sortCondition=LanguageResources().const.Name[1]):
if sortCondition == LanguageResources().const.Name[1]:
problemRecords = dao.query(problems).\
order_by(problems.c.problemName.asc(),
problems.c.problemDifficulty.asc())
# Difficulty ProblemName 정렬
elif sortCondition == LanguageResources().const.Difficulty[1]:
problemRecords = dao.query(problems).\
order_by(problems.c.problemDifficulty.asc(),
problems.c.problemName.asc())
return problemRecords
def update_registered_problem_info(courseId, problemId, key_dict):
error = None
dao.query(RegisteredProblems).\
filter(and_(RegisteredProblems.courseId == courseId,
RegisteredProblems.problemId == problemId)).\
update(key_dict)
try:
dao.commit()
except exc.SQLAlchemyError:
error = 'Error has been occurred while searching the problem to edit'
return error
def update_article_modify(articleIndex,
problemIndex,
articleType,
articleParameter,
isDeleted=ENUMResources().const.FALSE):
dao.query(ArticlesOnBoard).\
filter(ArticlesOnBoard.articleIndex == articleIndex,
ArticlesOnBoard.isDeleted == isDeleted).\
update(dict(problemIndex = problemIndex,
articleType = articleType,
title = articleParameter.title,
content = articleParameter.content,
updateIp = articleParameter.updateIp,
updateDate = articleParameter.updateDate))
def search_submissions(submissions, filterFindParameter):
# condition은 All, courseName, problemName, memberId로 나누어서 검새
if filterFindParameter.filterCondition == LanguageResources().const.All[1]: # Filters[0] is '모두'
submissions = dao.query(submissions).\
filter(or_(submissions.c.problemName.like('%' + filterFindParameter.keyWord + '%'),
submissions.c.memberId == filterFindParameter.keyWord))
elif filterFindParameter.filterCondition == LanguageResources().const.Problem[1]: # Filters[2] is 'ProblemName'
submissions = dao.query(submissions).\
filter(submissions.c.problemName.like('%' + filterFindParameter.keyWord + '%'))
else: # Filters[3] is 'MembmerId'
submissions = dao.query(submissions).\
filter(submissions.c.memberId == filterFindParameter.keyWord)
return submissions
def select_sorted_articles(articlesOnBoard, isNotice = ENUMResources().const.FALSE, filterFindParameter = FilterFindParameter()):
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.isNotice == isNotice,
(or_(articlesOnBoard.c.courseName == None,
articlesOnBoard.c.courseName != None) if isNotice == ENUMResources().const.TRUE
else articlesOnBoard.c.courseName != None)).\
subquery()
# Filter Case
if filterFindParameter\
and filterFindParameter.filterCondition:
articlesOnBoard = search_articles(articlesOnBoard,
filterFindParameter).subquery()
return dao.query(articlesOnBoard).\
order_by(articlesOnBoard.c.articleIndex.desc())
def join_registrations_submissions(members, submissions):
submissions = dao.query(members,
submissions.c.problemIndex,
submissions.c.status,
submissions.c.codeSubmissionDate,
submissions.c.runTime,
submissions.c.usedMemory,
submissions.c.sumOfSubmittedFileSize,
submissions.c.score).\
outerjoin(submissions,
members.c.memberIdIndex == submissions.c.memberIdIndex).\
subquery()
submissions = dao.query(submissions).\
order_by(submissions.c.memberId.asc())
return submissions
def join_problems_name(subquery, subProblemIndex, isDeleted = ENUMResources().const.FALSE):
return dao.query(subquery,
Problems.problemName,
Problems.solutionCheckType,
Problems.problemPath).\
outerjoin(Problems,
Problems.problemIndex == subProblemIndex)
def search_submissions(submissions, filterFindParameter):
# condition은 All, courseName, problemName, memberId로 나누어서 검새
if filterFindParameter.filterCondition == LanguageResources(
).const.All[1]: # Filters[0] is '모두'
submissions = dao.query(submissions).\
filter(or_(submissions.c.problemName.like('%' + filterFindParameter.keyWord + '%'),
submissions.c.memberId == filterFindParameter.keyWord))
elif filterFindParameter.filterCondition == LanguageResources(
).const.Problem[1]: # Filters[2] is 'ProblemName'
submissions = dao.query(submissions).\
filter(submissions.c.problemName.like('%' + filterFindParameter.keyWord + '%'))
else: # Filters[3] is 'MembmerId'
submissions = dao.query(submissions).\
filter(submissions.c.memberId == filterFindParameter.keyWord)
return submissions
def select_replies_on_code(submissionIndex,
submissionReplyIndex=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(RepliesOnSubmission).\
filter((RepliesOnSubmission.submissionIndex == submissionIndex if submissionIndex
else RepliesOnSubmission.submissionReplyIndex == submissionReplyIndex),
RepliesOnSubmission.isDeleted == isDeleted)
def join_registrations_submissions(members, submissions):
submissions = dao.query(members,
submissions.c.problemIndex,
submissions.c.status,
submissions.c.codeSubmissionDate,
submissions.c.runTime,
submissions.c.usedMemory,
submissions.c.sumOfSubmittedFileSize,
submissions.c.score).\
outerjoin(submissions,
members.c.memberIdIndex == submissions.c.memberIdIndex).\
subquery()
submissions = dao.query(submissions).\
order_by(submissions.c.memberId.asc())
return submissions
def select_replies_on_board(articleIndex,
boardReplyIndex=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(RepliesOnBoard).\
filter(and_((RepliesOnBoard.articleIndex == articleIndex if articleIndex
else RepliesOnBoard.boardReplyIndex == boardReplyIndex),
RepliesOnBoard.isDeleted == isDeleted))
def select_languages(courseId):
return dao.query(LanguagesOfCourses.languageIndex,
Languages.languageName,
Languages.languageVersion).\
join(Languages,
LanguagesOfCourses.languageIndex == Languages.languageIndex).\
filter(LanguagesOfCourses.courseId == courseId)
def search_articles(articlesOnBoard, filterFindParameter = FilterFindParameter()):
# condition은 All, Id, Title&Content로 나누어서 검새
if filterFindParameter.filterCondition == '모두': # Filters[0] is '모두'
articlesOnBoard = dao.query(articlesOnBoard).\
filter(or_(articlesOnBoard.c.writerId.like('%' + filterFindParameter.keyWord + '%'),
articlesOnBoard.c.title.like('%' + filterFindParameter.keyWord + '%'),
articlesOnBoard.c.content.like('%' + filterFindParameter.keyWord + '%')))
elif filterFindParameter.filterCondition == '작성자': # Filters[1] is '작성자'
articlesOnBoard = dao.query(articlesOnBoard).\
filter(articlesOnBoard.c.writerId.like('%' + filterFindParameter.keyWord + '%'))
else: # Filters[2] is '제목&내용'
articlesOnBoard = dao.query(articlesOnBoard).\
filter(or_(articlesOnBoard.c.title.like('% '+ filterFindParameter.keyWord + '%'),
articlesOnBoard.c.content.like('%' + filterFindParameter.keyWord + '%')))
return articlesOnBoard
def select_problem(problemIndex,
problemName=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(Problems).\
filter(and_((Problems.problemIndex == problemIndex if problemIndex
else Problems.problemName == problemName),
Problems.isDeleted == isDeleted))
def select_problems(problemDifficulty=None,
isDeleted=ENUMResources().const.FALSE):
return dao.query(Problems).\
filter((Problems.problemDifficulty == problemDifficulty if problemDifficulty
else Problems.problemDifficulty != problemDifficulty),
Problems.isDeleted == isDeleted).\
order_by(Problems.problemIndex.asc())
def join_member_informations(members):
return (
dao.query(members, Colleges.collegeName, Departments.departmentName)
.outerjoin(DepartmentsDetailsOfMembers, members.c.memberId == DepartmentsDetailsOfMembers.memberId)
.outerjoin(Colleges, Colleges.collegeIndex == DepartmentsDetailsOfMembers.collegeIndex)
.outerjoin(Departments, Departments.departmentIndex == DepartmentsDetailsOfMembers.departmentIndex)
)
def check_invitee_member(inviteeId, teamName = None):
# 인풋 폼안에 아이디가 있을 때
if inviteeId:
# 존재 하는 사용자 인지 확인
if not dao.query(select_match_member(memberId = inviteeId).subquery()).\
first():
return get_message('notExists')
# 자가 자신 초대 방지
elif inviteeId == session[SessionResources().const.MEMBER_ID]:
return get_message('notSelf')
# MakeTeam In Invitee
elif not teamName:
# 초대 한 애를 또 초대 하는거를 방지
if inviteeId in gTeamMembersId:
return get_message('alreadyExists')
# Invitee Id Add
gTeamMembersId.append(inviteeId)
return None
# ManageTeam In Invitee
else:
# 초대 중복 방지
if dao.query(TeamInvitations).\
filter(TeamInvitations.teamName == teamName,
TeamInvitations.inviteeId == inviteeId,
TeamInvitations.isDeleted == ENUMResources().const.FALSE).\
first():
return get_message('alreadyExists')
# 팀원 초대 방지
elif dao.query(RegisteredTeamMembers.teamMemberId).\
filter(RegisteredTeamMembers.teamName == teamName,
RegisteredTeamMembers.teamMemberId == inviteeId,
RegisteredTeamMembers.isDeleted == ENUMResources().const.FALSE).\
first():
return get_message('notTeamMemberInvitee')
# 조건에 충족 될 때
else:
return insert_invitee_id(teamName,
inviteeId)
# None 값 일 때
else:
return '아이디' + get_message('fillData')
def select_last_submissions(memberCourseProblemParameter=MemberCourseProblemParameter()):
if memberCourseProblemParameter.courseId == OtherResources().const.ALL or not (
memberCourseProblemParameter.memberId
or memberCourseProblemParameter.courseId
or memberCourseProblemParameter.problemId
):
return dao.query(
Submissions.memberId,
Submissions.courseId,
Submissions.problemId,
Submissions.codeSubmissionDate,
func.max(Submissions.submissionCount).label(OtherResources().const.SUBMISSION_COUNT),
func.max(Submissions.solutionCheckCount).label(OtherResources().const.SOLUTION_CHECK_COUNT),
func.max(Submissions.viewCount).label(OtherResources().const.VIEW_COUNT),
).group_by(Submissions.memberId, Submissions.problemId, Submissions.courseId)
else:
return (
dao.query(
Submissions.memberId,
Submissions.courseId,
Submissions.problemId,
Submissions.codeSubmissionDate,
func.max(Submissions.submissionCount).label(OtherResources().const.SUBMISSION_COUNT),
func.max(Submissions.solutionCheckCount).label(OtherResources().const.SOLUTION_CHECK_COUNT),
func.max(Submissions.viewCount).label(OtherResources().const.VIEW_COUNT),
)
.filter(
(
Submissions.courseId == memberCourseProblemParameter.courseId
if memberCourseProblemParameter.courseId
else Submissions.courseId != None
),
(
Submissions.problemId == memberCourseProblemParameter.problemId
if memberCourseProblemParameter.problemId
else Submissions.problemId != None
),
(
Submissions.memberId == memberCourseProblemParameter.memberId
if memberCourseProblemParameter.memberId
else Submissions.memberId != None
),
)
.group_by(Submissions.memberId, Submissions.problemId, Submissions.courseId)
)
def insert_team_member_id(teamName, teamMemberId, isTeamMaster = ENUMResources().const.FALSE):
# if not exist Records then Insert
if not dao.query(RegisteredTeamMembers).\
filter(RegisteredTeamMembers.teamName == teamName,
RegisteredTeamMembers.teamMemberId == teamMemberId).\
first():
dao.add(RegisteredTeamMembers(teamName = teamName,
teamMemberId = teamMemberId,
isTeamMaster = isTeamMaster))
dao.commit()
# else then Update
else:
dao.query(RegisteredTeamMembers).\
filter(RegisteredTeamMembers.teamName == teamName,
RegisteredTeamMembers.teamMemberId == teamMemberId).\
update(dict(isDeleted = ENUMResources().const.FALSE,
isTeamMaster =isTeamMaster))
def get_used_language_version(usedLanguage):
try:
usedLanguageVersion = dao.query(Languages.languageVersion).\
first().\
languageVersion
except:
return unknown_error(get_message('dbError'))
return usedLanguageVersion
def select_data_of_submission_board(submissionIndex, memberIdIndex = None, problemIndex = None):
# 어떤 소속의 특정 과목 또는 문제 또는 인원에 대하여
return dao.query(DataOfSubmissionBoard).\
filter(DataOfSubmissionBoard.submissionIndex == submissionIndex if submissionIndex
else (DataOfSubmissionBoard.problemIndex == problemIndex if problemIndex
else DataOfSubmissionBoard.problemIndex != None),
(DataOfSubmissionBoard.memberIdIndex == memberIdIndex if memberIdIndex
else DataOfSubmissionBoard.memberIdIndex != None))
def search_members(members, filterFindParameter, isDeleted = ENUMResources().const.FALSE):
# condition은 All, ID, Name로 나누어서 검새
if filterFindParameter.filterCondition == LanguageResources().const.All[1]: # Filters[0] is '모두'
members = dao.query(members).\
filter(or_(members.c.memberId == filterFindParameter.keyWord,
members.c.memberName.like('%' + filterFindParameter.keyWord + '%')),
members.c.isDeleted == isDeleted)
elif filterFindParameter.filterCondition == LanguageResources().const.ID[1]: # Filters[1] is ID
members = dao.query(members).\
filter(members.c.memberId == filterFindParameter.keyWord,
members.c.isDeleted == isDeleted)
else: # Filters[2] is 'NAme'
members = dao.query(members).\
filter(members.c.memberName.like('%'+ filterFindParameter.keyWord + '%'),
members.c.isDeleted == isDeleted)
return members
def join_member_id(subquery,
subMemberIdIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Members.memberId).\
join(Members,
and_(Members.memberIdIndex == subMemberIdIndex,
Members.isDeleted == isDeleted))
def join_languages_name(subquery,
subLanguageIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Languages.languageName,
Languages.languageVersion).\
outerjoin(Languages,
Languages.languageIndex == subLanguageIndex)
def get_used_language_index(usedLanguageName):
try:
usedLanguageIndex = dao.query(Languages.languageIndex).\
filter(Languages.languageName == usedLanguageName).\
first().\
languageIndex
except Exception as e:
return unknown_error(get_message('dbError'))
return usedLanguageIndex
def join_problems_name(subquery,
subProblemIndex,
isDeleted=ENUMResources().const.FALSE):
return dao.query(subquery,
Problems.problemName,
Problems.solutionCheckType,
Problems.problemPath).\
outerjoin(Problems,
Problems.problemIndex == subProblemIndex)
def select_submissions_result(lastSubmission):
return dao.query(Submissions.score,
Submissions.status,
Submissions.compileErrorMessage,
Submissions.wrongTestCaseNumber,
lastSubmission).\
join(lastSubmission,
and_(Submissions.submissionIndex == lastSubmission.c.submissionIndex,
Submissions.submissionCount == lastSubmission.c.submissionCount))
def get_used_language_index(usedLanguageName, usedLanguageVersion = None):
try:
usedLanguageIndex = dao.query(Languages.languageIndex).\
filter(Languages.languageName == usedLanguageName,
Languages.languageVersion == usedLanguageVersion).\
first().\
languageIndex
except:
return unknown_error(get_message('dbError'))
return usedLanguageIndex
def get_submission_index(memberIdIndex, problemIndex):
try:
submissionIndex = dao.query(DataOfSubmissionBoard.submissionIndex).\
filter(and_(DataOfSubmissionBoard.memberIdIndex == memberIdIndex,
DataOfSubmissionBoard.problemIndex == problemIndex)).first().\
submissionIndex
except:
dataOfSubmissionBoard = DataOfSubmissionBoard(memberIdIndex = memberIdIndex,
problemIndex = problemIndex)
dao.add(dataOfSubmissionBoard)
dao.commit()
submissionIndex = dao.query(DataOfSubmissionBoard.submissionIndex).\
filter(and_(DataOfSubmissionBoard.memberIdIndex == memberIdIndex,
DataOfSubmissionBoard.problemIndex == problemIndex)).first().\
submissionIndex
return submissionIndex
def select_members(Administrator=SETResources().const.ADMINISTRATOR,
User=SETResources().const.USER,
isDeleted=ENUMResources().const.FALSE):
# 자동 완성을 위한 모든 유저기록
members = dao.query(Members).\
filter(Members.authority == User,
Members.isDeleted == isDeleted)
return members
def select_data_of_submission_board(submissionIndex,
memberIdIndex=None,
problemIndex=None):
# 어떤 소속의 특정 과목 또는 문제 또는 인원에 대하여
return dao.query(DataOfSubmissionBoard).\
filter(DataOfSubmissionBoard.submissionIndex == submissionIndex if submissionIndex
else (DataOfSubmissionBoard.problemIndex == problemIndex if problemIndex
else DataOfSubmissionBoard.problemIndex != None),
(DataOfSubmissionBoard.memberIdIndex == memberIdIndex if memberIdIndex
else DataOfSubmissionBoard.memberIdIndex != None))
def select_problem_chart_submissions(sumOfSubmissionPeopleCount,
sumOfSolvedPeopleCount,
problemSubmittedRecords):
return dao.query(sumOfSubmissionPeopleCount, sumOfSolvedPeopleCount,
problemSubmittedRecords.c.sumOfSubmissionCount,
problemSubmittedRecords.c.sumOfSolvedCount,
problemSubmittedRecords.c.sumOfWrongCount,
problemSubmittedRecords.c.sumOfTimeOverCount,
problemSubmittedRecords.c.sumOfMemoryOverFlowCount,
problemSubmittedRecords.c.sumOfCompileErrorCount,
problemSubmittedRecords.c.sumOfRuntimeErrorCount)
def join_problem_lists_submissions(problems, submissions):
return dao.query(problems,
submissions.c.score,
submissions.c.status,
submissions.c.submissionCount,
submissions.c.solutionCheckCount,
submissions.c.compileErrorMessage,
submissions.c.wrongTestCaseNumber).\
outerjoin(submissions,
problems.c.problemIndex == submissions.c.problemIndex).\
order_by(problems.c.problemName.asc())