def view(self, student_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') l_user = DBSession.query(User).filter(User.user_name == login).one() user = DBSession.query(User).filter(User.id == int(student_id)).first() if not user: redirect('/error', params=dict(msg="invalid user")) courses_results = {} courses_taught = self._get_courses_taught(l_user) sql_s = """ SELECT p.id as paper_id_p, u.*, s.id as summary_id, s.*, p.*, z.avg_rating FROM users u JOIN user_group ug on u.id = ug.user_id JOIN groups g on ug.group_id = g.id JOIN courses c on c.students_id = g.id JOIN papers p on c.id = p.course_id LEFT JOIN paper_summaries s ON s.paper_id = p.id LEFT JOIN (SELECT s.id, round(avg((r.rating+r.insight_rating)/2),2) as avg_rating FROM paper_summaries s JOIN summary_reviews r on s.id = r.summary_id WHERE s.student_id = :user_id AND r.status = 'complete' GROUP BY s.id ) z on s.id = z.id WHERE u.id = :user_id and c.id = :course_id and (s.student_id =:user_id or s.student_id is null)""" for course in courses_taught: summaries = DBSession.execute(sql_s, dict(course_id=course.id, user_id=user.id)).fetchall() courses_results[course] = summaries return dict(page="viewstudent", student=user, courses=courses_results)
def view(self, paper_id): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() paper = DBSession.query(Paper)\ .filter(Paper.id == int(paper_id)).first() if not paper: redirect('/error', params=dict(msg="invalid paper")) if not self._can_view_paper(user, paper): redirect('/error', params=dict(msg="invalid permissions to view paper")) # submissions and their avg ratings sql_s = """ SELECT u.id as user_id, u.*, s.id as summary_id, s.*, z.avg_rating FROM users u JOIN user_group ug on u.id = ug.user_id JOIN groups g on ug.group_id = g.id JOIN courses c on c.students_id = g.id JOIN papers p on c.id = p.course_id LEFT JOIN paper_summaries s ON s.paper_id = p.id and s.student_id = u.id LEFT JOIN (SELECT s.id, round(avg((r.rating + r.insight_rating)/2),2) as avg_rating FROM paper_summaries s JOIN summary_reviews r on s.id = r.summary_id WHERE s.paper_id = :paper_id AND r.status = 'complete' GROUP BY s.id ) z on s.id = z.id WHERE p.id = :paper_id""" summaries = DBSession.execute(sql_s, dict(paper_id=paper_id)).fetchall() return dict(page="viewpaper", paper=paper, summaries=summaries)
def view(self, student_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') l_user = DBSession.query(User).filter(User.user_name == login).one() user = DBSession.query(User).filter(User.id == int(student_id)).first() if not user: redirect('/error', params=dict(msg="invalid user")) courses_results = {} courses_taught = self._get_courses_taught(l_user) courses_taught = filter(lambda c: user in c.students.users, courses_taught) sql_s = """ SELECT p.id as paper_id_p, u.*, s.id as summary_id, s.*, p.*, z.avg_rating FROM users u JOIN user_group ug on u.id = ug.user_id JOIN groups g on ug.group_id = g.id JOIN courses c on c.students_id = g.id JOIN papers p on c.id = p.course_id LEFT JOIN paper_summaries s ON s.paper_id = p.id and s.student_id = u.id LEFT JOIN (SELECT s.id, round(avg((r.rating+r.insight_rating)/2),2) as avg_rating FROM paper_summaries s JOIN summary_reviews r on s.id = r.summary_id WHERE s.student_id = :user_id AND r.status = 'complete' GROUP BY s.id ) z on s.id = z.id WHERE u.id = :user_id and c.id = :course_id""" for course in courses_taught: summaries = DBSession.execute(sql_s, dict(course_id=course.id, user_id=user.id)).fetchall() courses_results[course] = summaries reviews = DBSession.query(SummaryReview, PaperSummary, User, Paper)\ .join(SummaryReview.summary).join(PaperSummary.student)\ .join(PaperSummary.paper).filter(SummaryReview.creator_id == user.id).all() return dict(page="viewstudent", student=user, courses=courses_results, reviews=reviews)
def new(self, course_id): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).one() if user not in course.instructors.users: redirect('/error', params=dict(msg="invalid permissions to view course")) return dict(page="createpaper", course=course, paper=None)
def view(self, summary_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() summary = DBSession.query(PaperSummary).filter(PaperSummary.id == int(summary_id)).first() if not summary: redirect('/error', params=dict(msg="invalid paper summary")) if not self._can_view_summary(summary, user): redirect('/error', params=dict(msg="invalid permissions to view summary")) paper = summary.paper return dict(page="viewreview", paper=paper, summary=summary)
def new(self, course_id): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() course = DBSession.query(Course).filter( Course.id == int(course_id)).one() if user not in course.instructors.users: redirect('/error', params=dict(msg="invalid permissions to view course")) return dict(page="createpaper", course=course, paper=None)
def view(self, summary_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() summary = DBSession.query(PaperSummary).filter(PaperSummary.id == int(summary_id)).first() course = summary.paper.course is_instructor = user in course.instructors.users if not summary: redirect('/error', params=dict(msg="invalid paper summary")) if not self._can_view_summary(summary, user): redirect('/error', params=dict(msg="invalid permissions to view summary")) paper = summary.paper return dict(page="viewreview", paper=paper, summary=summary, is_instructor=is_instructor)
def edit(self, paper_id): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() paper = DBSession.query(Paper)\ .filter(Paper.id == int(paper_id)).first() if not paper: redirect('/error', params=dict(msg="invalid paper")) if not self._can_view_paper(user, paper): redirect('/error', params=dict(msg="invalid permissions to view paper")) return dict(page="editpaper", paper=paper, course=paper.course)
def view(self, course_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) papers = course.papers students = course.students.users instructors = course.instructors.users return dict(page="course",course=course, students=students, instructors=instructors, papers=papers)
def view(self, course_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) papers = course.papers students = DBSession.execute("SELECT * FROM enrolled_students_overview where course_id = :course_id order by display_name", dict(course_id=course.id)).fetchall() s_avg_rating = StdDevCalculator(map(lambda x: x.avg_rating, students), True) s_avg_reading = StdDevCalculator(map(lambda x: x.avg_reading, students), True) s_avg_insight = StdDevCalculator(map(lambda x: x.avg_insight, students), True) s_avg_nz_rating = StdDevCalculator(map(lambda x: x.avg_nonzero_rating, students)) s_avg_nz_reading = StdDevCalculator(map(lambda x: x.avg_nonzero_reading, students)) s_avg_nz_insight = StdDevCalculator(map(lambda x: x.avg_nonzero_insight, students)) s_avg_submitted = StdDevCalculator(map(lambda x: x.submitted_papers, students), True) instructors = course.instructors.users reviews = DBSession.query(SummaryReview, User, Paper)\ .join(SummaryReview.summary).join(PaperSummary.student)\ .join(PaperSummary.paper)\ .filter(Paper.course_id == course.id)\ .filter(SummaryReview.comments != '').all() sql_s = """select * from (select user_id, display_name, avg(av) as avg_, variance(av) as var_, avg(length(comments)) as avg_len, count(distinct comments)-1 num_nonnull from ( select sr.comments, sr.id, u.id as user_id, u.display_name, u.user_name, (rating+insight_rating)/2 as av from summary_reviews sr join paper_summaries ps on sr.summary_id = ps.id join papers p on ps.paper_id = p.id join users u on sr.creator_id = u.id where course_id = :course_id) z group by user_id, display_name) a left join (select creator_id, avg(diff) as avg_diff from (select creator_id, avg_rating, (insight_rating+rating)/2, abs(avg_rating-(insight_rating+rating)/2) as diff from graded_paper_summaries gps join summary_reviews sr on gps.summary_id = sr.summary_id join papers p on p.id = gps.paper_id where p.course_id = :course_id and status = 'complete') z group by creator_id) z on a.user_id = z.creator_id;""" feedback = DBSession.execute(sql_s, dict(course_id = course.id)).fetchall() s_f_avg = StdDevCalculator(map(lambda x: x.avg_, feedback)) s_f_var = StdDevCalculator(map(lambda x: x.var_, feedback)) s_f_avglen = StdDevCalculator(map(lambda x: x.avg_len, feedback)) s_f_nonnull = StdDevCalculator(map(lambda x: x.num_nonnull, feedback)) s_f_distavg = StdDevCalculator(map(lambda x: x.avg_diff, feedback)) return dict(page="course",course=course, students=students, feedback=feedback, instructors=instructors, papers=papers, reviews=reviews, s_f_avg=s_f_avg, s_f_var=s_f_var, s_f_avglen=s_f_avglen, s_f_nonnull=s_f_nonnull, s_f_distavg=s_f_distavg, s_avg_rating=s_avg_rating, s_avg_reading=s_avg_reading, s_avg_insight=s_avg_insight, s_avg_nz_rating = s_avg_nz_rating, s_avg_nz_reading=s_avg_nz_reading, s_avg_nz_insight=s_avg_nz_insight, s_avg_submitted=s_avg_submitted )
def _reset_password(self, password1, password2): if password1 != password2: redirect("/error") login = request.environ.get("repoze.who.identity").get("repoze.who.userid") user = DBSession.query(User).filter(User.user_name == login).one() user.password = password1 redirect("/account")
def scores_given_cdf(self, course_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) d = [] i = 0 for student in course.students.users: d.append(dict(key=student.display_name, values=self.cdf_user_course_given(course.id, student.id))) i += 1 if i > 9: break return dict(r=d)
def index(self): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() summaries = DBSession.execute(""" SELECT s.id as summary_id, s.*, p.*, z.avg_rating, z.num_reviews as num_reviews FROM papers p join paper_summaries s on p.id = s.paper_id LEFT JOIN ( SELECT s.id as id, round(avg((r.rating+r.insight_rating)/2),2) as avg_rating, count(r.id) as num_reviews FROM paper_summaries s JOIN summary_reviews r ON s.id = r.summary_id WHERE r.status = 'complete' GROUP BY s.id ) z on s.id = z.id WHERE s.student_id = :user_id""", dict(user_id=user.id)) return dict(page="summaries", summaries=summaries)
def save(self, paper_id, title, downloadurl, abstract, course_id, duedate): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).one() if paper_id != '': paper = DBSession.query(Paper)\ .filter(Paper.id == int(paper_id)).first() if not paper: redirect('/error', params=dict(msg="invalid paper")) if not self._can_view_paper(user, paper): redirect('/error', params=dict(msg="invalid permissions to view paper")) paper.name = str(title) paper.download_url = str(downloadurl) paper.abstract = str(abstract) paper.due_date = duedate else: paper = Paper() punctuation = { 0x2018:0x27, 0x2019:0x27, 0x201C:0x22, 0x201D:0x22 } paper.name = title.translate(punctuation).encode('ascii', 'replace') paper.download_url = downloadurl paper.abstract = abstract.encode('ascii', 'replace') paper.created_by = user.id paper.due_date = duedate paper.course_id = course_id DBSession.add(paper) DBSession.flush() redirect('/paper/view?paper_id=%i' % paper.id)
def score_cdf(self, course_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter(Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) d = [ {'key':'Avg', 'values':self.get_cdf(course.id, 'avg_rating')}, {'key':'Reading', 'values':self.get_cdf(course.id, 'avg_reading')}, {'key':'Insight', 'values':self.get_cdf(course.id, 'avg_insight')}, {'key':'NZAvg', 'values':self.get_cdf(course.id, 'avg_nonzero_rating')}, {'key':'NZRead', 'values':self.get_cdf(course.id, 'avg_nonzero_reading')}, {'key':'NZInght', 'values':self.get_cdf(course.id, 'avg_nonzero_insight')}, ] return dict(r=d)
def get_cdf(self, course_id, name): raw = DBSession.execute("select %s, count(user_id) as count from enrolled_students_overview where %s > 0 and course_id = :course_id group by %s order by %s" % (name, name, name, name), dict(course_id=course_id)).fetchall() total = sum(map(lambda raw: getattr(raw, "count"), raw)) total_so_far = 0 cdf = [] for r in raw: total_so_far = total_so_far + r.count cdf.append(dict(x=getattr(r, name), y=float(total_so_far)/float(total), size=5)) return cdf
def create(self, paper_id): login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() # does this paper even exist? paper = self._check_valid_paper(int(paper_id)) if not paper: redirect('/error', params=dict(msg="invalid paper supplied")) # does this user have access to this paper? if not self._check_eligible_submit(user, paper): redirect('/error', params=dict( msg="user does not have access to paper")) # has this user already submitted this paper? if self._check_user_submitted_paper(user, paper): redirect('/error', params=dict( msg="user already submitted summary for this paper")) # check if the user has any "reserved" summaries that they need to review # this prevents users from just refreshing and getting new summaries until # they receive their friends summary or waiting until all the others have # been completed by other students. reserved = self._get_unfinished_peer_reviews(user) num_adtl_needed = self.MAX_PEER_REVIEWS - len(reserved) new_summaries = self._get_summaries_to_peer_review(user, num_adtl_needed) # for each of the new reviews, we need to start an "incomplete" review so # that it resurfaces in the future. summaries_to_review = [] for summary in reserved: summaries_to_review.append(summary) for summary in new_summaries: review = StudentSummaryReview( summary = summary, creator = user, status = 'incomplete' ) DBSession.add(review) summaries_to_review.append(summary) hmacs = {} for s in summaries_to_review: hmacs[s.id] = self._generate_peer_review_hmac(user, s) return dict(page="newreview", paper=paper, peer_review=summaries_to_review, hmacs=hmacs)
def scores_given_cdf(self, course_id): login = request.environ.get('repoze.who.identity').get( 'repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter( Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) d = [] i = 0 for student in course.students.users: d.append( dict(key=student.display_name, values=self.cdf_user_course_given(course.id, student.id))) i += 1 if i > 9: break return dict(r=d)
def index(self): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() courses_e = {} for course in user.courses_enrolled_in: papers = DBSession.execute("""SELECT s.id as summary_id, s.*, p.*, p.id as p_paper_id, z.avg_rating, z.num_reviews as num_reviews FROM papers p LEFT JOIN ( SELECT id, paper_id from paper_summaries WHERE student_id = :user_id) s ON p.id = s.paper_id LEFT JOIN ( SELECT s2.id as id, round(avg((r.rating+r.insight_rating)/2),2) as avg_rating, count(r.id) as num_reviews FROM paper_summaries s2 JOIN summary_reviews r ON s2.id = r.summary_id WHERE r.status = 'complete' GROUP BY s2.id ) z on s.id = z.id WHERE p.course_id = :course_id""", dict(user_id=user.id, course_id = course.id)).fetchall() courses_e[course] = papers return dict(page="course", courses_enrolled=courses_e, courses_taught=user.courses_taught)
def cdf_user_course_given(self, course_id, user_id): sql_s = """select av, count(id) as count from ( select sr.id, u.id as user_id, u.display_name, u.user_name, (rating+insight_rating)/2 as av from summary_reviews sr join paper_summaries ps on sr.summary_id = ps.id join papers p on ps.paper_id = p.id join users u on sr.creator_id = u.id where course_id = :course_id and sr.creator_id = :user_id) z group by av order by av;""" raw = DBSession.execute(sql_s, dict(course_id=course_id, user_id=user_id)).fetchall() total = sum(map(lambda raw: getattr(raw, "count"), raw)) total_so_far = 0 cdf = [] for r in raw: total_so_far = total_so_far + r.count cdf.append(dict(x=getattr(r, "av"), y=float(total_so_far)/float(total))) return cdf
def _reset_password(self, password1, password2, old_password): if password1 != password2: redirect("/error") if len(password1) > 256 or len(password1) < 6: redirect("/error") login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() if not user.validate_password(old_password): flash(_('Error! Incorrect original password.'), 'warning') redirect('/account') user.password = password1 redirect('/account')
def score_cdf(self, course_id): login = request.environ.get('repoze.who.identity').get( 'repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter( Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) d = [ { 'key': 'Avg', 'values': self.get_cdf(course.id, 'avg_rating') }, { 'key': 'Reading', 'values': self.get_cdf(course.id, 'avg_reading') }, { 'key': 'Insight', 'values': self.get_cdf(course.id, 'avg_insight') }, { 'key': 'NZAvg', 'values': self.get_cdf(course.id, 'avg_nonzero_rating') }, { 'key': 'NZRead', 'values': self.get_cdf(course.id, 'avg_nonzero_reading') }, { 'key': 'NZInght', 'values': self.get_cdf(course.id, 'avg_nonzero_insight') }, ] return dict(r=d)
def get_cdf(self, course_id, name): raw = DBSession.execute( "select %s, count(user_id) as count from enrolled_students_overview where %s > 0 and course_id = :course_id group by %s order by %s" % (name, name, name, name), dict(course_id=course_id)).fetchall() total = sum(map(lambda raw: getattr(raw, "count"), raw)) total_so_far = 0 cdf = [] for r in raw: total_so_far = total_so_far + r.count cdf.append( dict(x=getattr(r, name), y=float(total_so_far) / float(total), size=5)) return cdf
def index(self): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() courses_e = {} for course in user.courses_enrolled_in: papers = DBSession.execute( """SELECT s.id as summary_id, s.*, p.*, p.id as p_paper_id, z.avg_rating, z.num_reviews as num_reviews FROM papers p LEFT JOIN ( SELECT id, paper_id from paper_summaries WHERE student_id = :user_id) s ON p.id = s.paper_id LEFT JOIN ( SELECT s2.id as id, round(avg((r.rating+r.insight_rating)/2),2) as avg_rating, count(r.id) as num_reviews FROM paper_summaries s2 JOIN summary_reviews r ON s2.id = r.summary_id WHERE r.status = 'complete' GROUP BY s2.id ) z on s.id = z.id WHERE p.course_id = :course_id""", dict(user_id=user.id, course_id=course.id)).fetchall() courses_e[course] = papers return dict(page="course", courses_enrolled=courses_e, courses_taught=user.courses_taught)
def cdf_user_course_given(self, course_id, user_id): sql_s = """select av, count(id) as count from ( select sr.id, u.id as user_id, u.display_name, u.user_name, (rating+insight_rating)/2 as av from summary_reviews sr join paper_summaries ps on sr.summary_id = ps.id join papers p on ps.paper_id = p.id join users u on sr.creator_id = u.id where course_id = :course_id and sr.creator_id = :user_id) z group by av order by av;""" raw = DBSession.execute(sql_s, dict(course_id=course_id, user_id=user_id)).fetchall() total = sum(map(lambda raw: getattr(raw, "count"), raw)) total_so_far = 0 cdf = [] for r in raw: total_so_far = total_so_far + r.count cdf.append( dict(x=getattr(r, "av"), y=float(total_so_far) / float(total))) return cdf
def setUp(self): """Prepare model test fixture.""" try: new_attrs = {} new_attrs.update(self.attrs) new_attrs.update(self.do_get_dependencies()) self.obj = self.klass(**new_attrs) DBSession.add(self.obj) DBSession.flush() return self.obj except: DBSession.rollback() raise
def save(self, paper_id, title, downloadurl, abstract, course_id, duedate): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User)\ .filter(User.user_name == login).one() course = DBSession.query(Course).filter( Course.id == int(course_id)).one() if paper_id != '': paper = DBSession.query(Paper)\ .filter(Paper.id == int(paper_id)).first() if not paper: redirect('/error', params=dict(msg="invalid paper")) if not self._can_view_paper(user, paper): redirect('/error', params=dict(msg="invalid permissions to view paper")) paper.name = str(title) paper.download_url = str(downloadurl) paper.abstract = str(abstract) paper.due_date = duedate else: paper = Paper() punctuation = { 0x2018: 0x27, 0x2019: 0x27, 0x201C: 0x22, 0x201D: 0x22 } paper.name = title.translate(punctuation).encode( 'ascii', 'replace') paper.download_url = downloadurl paper.abstract = abstract.encode('ascii', 'replace') paper.created_by = user.id paper.due_date = duedate paper.course_id = course_id DBSession.add(paper) DBSession.flush() redirect('/paper/view?paper_id=%i' % paper.id)
def _get_summaries_to_peer_review(self, user, num_summaries=2): # restrictions we want: # 1. Already submitted review for the paper # 2. Did not write summary themselves # # orderings we want # 1. papers with the least number of reviews # # because we're doing a join back to themselves... sqlalchemy # is going to be obnoxious, so let's just write some sql ourselves sql_s = """select s.*, s.id as summary_id, count from paper_summaries s join (select s.id, count(r.id) as count from paper_summaries s LEFT JOIN summary_reviews r on s.id = r.summary_id where s.paper_id in (select paper_id FROM paper_summaries WHERE student_id = :user_id) AND s.student_id <> :user_id GROUP BY s.id ORDER BY count(r.id), RAND() DESC LIMIT %i) z ON s.id = z.id where s.id not in ( SELECT s.id from paper_summaries s JOIN summary_reviews r on s.id = r.summary_id WHERE r.creator_id = :user_id)""" % num_summaries return DBSession.query(PaperSummary)\ .from_statement(sql_s).params(user_id=user.id).all()
def _get_unfinished_peer_reviews(self, user): return DBSession.query(PaperSummary)\ .join(PaperSummary.reviews)\ .filter(StudentSummaryReview.creator_id == user.id)\ .filter(StudentSummaryReview.status == 'incomplete').all()
def get_courses_enrolled_in(self): return DBSession.query(Course)\ .join(Course.students).join(Group.users)\ .filter(User.id == self.id).all()
def account(self): login = request.environ.get('repoze.who.identity')\ .get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() return dict(user=user)
def view(self, course_id): login = request.environ.get('repoze.who.identity').get( 'repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() course = DBSession.query(Course).filter( Course.id == int(course_id)).first() if not course: redirect('/error', params=dict(msg="invalid course")) if not self._check_instructor_for_course(user, course): redirect('/error', params=dict(msg="user is not an instructor for course")) papers = course.papers students = DBSession.execute( "SELECT * FROM enrolled_students_overview where course_id = :course_id order by display_name", dict(course_id=course.id)).fetchall() s_avg_rating = StdDevCalculator(map(lambda x: x.avg_rating, students), True) s_avg_reading = StdDevCalculator( map(lambda x: x.avg_reading, students), True) s_avg_insight = StdDevCalculator( map(lambda x: x.avg_insight, students), True) s_avg_nz_rating = StdDevCalculator( map(lambda x: x.avg_nonzero_rating, students)) s_avg_nz_reading = StdDevCalculator( map(lambda x: x.avg_nonzero_reading, students)) s_avg_nz_insight = StdDevCalculator( map(lambda x: x.avg_nonzero_insight, students)) s_avg_submitted = StdDevCalculator( map(lambda x: x.submitted_papers, students), True) instructors = course.instructors.users reviews = DBSession.query(SummaryReview, User, Paper)\ .join(SummaryReview.summary).join(PaperSummary.student)\ .join(PaperSummary.paper)\ .filter(Paper.course_id == course.id)\ .filter(SummaryReview.comments != '').all() sql_s = """select * from (select user_id, display_name, avg(av) as avg_, variance(av) as var_, avg(length(comments)) as avg_len, count(distinct comments)-1 num_nonnull from ( select sr.comments, sr.id, u.id as user_id, u.display_name, u.user_name, (rating+insight_rating)/2 as av from summary_reviews sr join paper_summaries ps on sr.summary_id = ps.id join papers p on ps.paper_id = p.id join users u on sr.creator_id = u.id where course_id = :course_id) z group by user_id, display_name) a left join (select creator_id, avg(diff) as avg_diff from (select creator_id, avg_rating, (insight_rating+rating)/2, abs(avg_rating-(insight_rating+rating)/2) as diff from graded_paper_summaries gps join summary_reviews sr on gps.summary_id = sr.summary_id join papers p on p.id = gps.paper_id where p.course_id = :course_id and status = 'complete') z group by creator_id) z on a.user_id = z.creator_id;""" feedback = DBSession.execute(sql_s, dict(course_id=course.id)).fetchall() s_f_avg = StdDevCalculator(map(lambda x: x.avg_, feedback)) s_f_var = StdDevCalculator(map(lambda x: x.var_, feedback)) s_f_avglen = StdDevCalculator(map(lambda x: x.avg_len, feedback)) s_f_nonnull = StdDevCalculator(map(lambda x: x.num_nonnull, feedback)) s_f_distavg = StdDevCalculator(map(lambda x: x.avg_diff, feedback)) return dict(page="course", course=course, students=students, feedback=feedback, instructors=instructors, papers=papers, reviews=reviews, s_f_avg=s_f_avg, s_f_var=s_f_var, s_f_avglen=s_f_avglen, s_f_nonnull=s_f_nonnull, s_f_distavg=s_f_distavg, s_avg_rating=s_avg_rating, s_avg_reading=s_avg_reading, s_avg_insight=s_avg_insight, s_avg_nz_rating=s_avg_nz_rating, s_avg_nz_reading=s_avg_nz_reading, s_avg_nz_insight=s_avg_nz_insight, s_avg_submitted=s_avg_submitted)
def _get_courses_taught(self, user): return DBSession.query(Course)\ .join(Course.instructors)\ .join(Group.users)\ .filter(User.id == user.id).all()
def _check_valid_paper(self, paper_id): return DBSession.query(Paper).filter(Paper.id == paper_id).first()
def _create(self, paper_id, summary, **kwargs): punctuation = { 0x2018:0x27, 0x2019:0x27, 0x201C:0x22, 0x201D:0x22 } summary = summary.translate(punctuation).encode('ascii', 'replace') login = request.environ.get('repoze.who.identity').get('repoze.who.userid') user = DBSession.query(User).filter(User.user_name == login).one() paper = self._check_valid_paper(int(paper_id)) if not paper: redirect('/error', params=dict(msg="invalid paper supplied")) # does this user have access to this paper? if not self._check_eligible_submit(user, paper): redirect('/error', params=dict( msg="user does not have access to submit paper")) if self._check_user_submitted_paper(user, paper): redirect('/error', params=dict( msg="user already submitted summary for this paper")) # add summary summary_ = PaperSummary(paper, user, summary) DBSession.add(summary_) if kwargs: # we know that we have some summary reviews to grade # figure out how which summaries have been reviewed s = set() for arg in kwargs: name, val = arg.split("_") s.add(val) for summary_id in s: summary = DBSession.query(PaperSummary).filter(PaperSummary.id == summary_id).first() if not summary: redirect('/error', params=dict(msg="invalid summary id received")) k_hmac = "_".join(["hmac", summary_id]) if k_hmac not in kwargs: redirect('/error', params=dict(msg="incomplete requested received")) hmac = kwargs[k_hmac] if not self._check_peer_review_hmac(user, summary, str(hmac)): redirect('/error', params=dict(msg="invalid peer hmac received")) k_rating_reading = "_".join(["reading", summary_id]) if k_rating_reading not in kwargs: redirect('/error', params=dict(msg="incomplete requested received")) rating_reading = int(kwargs[k_rating_reading]) if rating_reading > 3 or rating_reading < 0: redirect('/error', params=dict(msg="invalid value for reading rating")) k_rating_critique = "_".join(["critique", summary_id]) if k_rating_critique not in kwargs: redirect('/error', params=dict(msg="incomplete requested received")) rating_critique = int(kwargs[k_rating_critique]) if rating_critique > 3 or rating_critique < 0: redirect('/error', params=dict(msg="invalid value for critique rating")) k_comments = "_".join(["comments", summary_id]) if k_comments not in kwargs: redirect('/error', params=dict(msg="incomplete requested received")) if self._has_already_submitted_review(user, summary): redirect('/error', params=dict(msg="user has already submitted a review for this summary")) comments = str(kwargs[k_comments]) # because we "reserved" this earlier, we should have # database records already in place that we just need # to update. if not then something is broken... response = DBSession.query(StudentSummaryReview)\ .filter(StudentSummaryReview.creator_id == user.id)\ .filter(StudentSummaryReview.summary_id == summary.id)\ .filter(StudentSummaryReview.status == 'incomplete').one() response.rating = rating_reading response.insight_rating = rating_critique response.comments = comments response.status = 'complete' redirect("/course")
def _check_user_submitted_paper(self, user, paper): return bool(DBSession.query(PaperSummary)\ .filter(PaperSummary.student_id == user.id)\ .filter(PaperSummary.paper_id == paper.id).first())
def _check_eligible_submit(self, user, paper): return bool(DBSession.query(User).join(User.groups)\ .join(Group.courses_enrolled_in)\ .join(Course.papers).filter(Paper.id == paper.id)\ .filter(User.id == user.id).first())
def by_email_address(cls, email): """Return the user object whose email address is ``email``.""" return DBSession.query(cls).filter_by(email_address=email).first()
def _has_already_submitted_review(self, user, summary): return bool(DBSession.query(StudentSummaryReview)\ .filter(StudentSummaryReview.creator_id == user.id)\ .filter(StudentSummaryReview.status == 'complete')\ .filter(StudentSummaryReview.summary_id == summary.id).first())
def by_user_name(cls, username): """Return the user object whose user name is ``username``.""" return DBSession.query(cls).filter_by(user_name=username).first()
def tearDown(self): """Finish model test fixture.""" DBSession.rollback()
def test_query_obj(self): """Model objects can be queried""" obj = DBSession.query(self.klass).one() for key, value in self.attrs.iteritems(): assert_equals(getattr(obj, key), value)
def get_courses_taught(self): return DBSession.query(Course).join(Course.instructors)\ .join(Group.users).filter(User.id == self.id).all()