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