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 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 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 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())