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