1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
from django.shortcuts import render
from django.db.models import Count
from django.db import connection
from models import ConferenceFeedbackQuestion, ConferenceFeedbackAnswer
from backendviews import get_authenticated_conference
from collections import OrderedDict
def build_graphdata(question, key, options):
optionhash = OrderedDict(zip(options, [0] * len(options)))
for answer in ConferenceFeedbackAnswer.objects.filter(conference=question.conference, question=question).order_by(key).values(key).annotate(Count(key)):
optionhash[answer[key]] = answer['%s__count' % key]
return optionhash.iteritems()
def build_feedback_response(question):
r = {'question': question.question, 'id': question.id, }
confid = question.conference.id
questionid = question.id
if question.isfreetext:
# This can actually be either freetext *or* graph!
if question.textchoices:
r['graphdata'] = build_graphdata(question, 'textanswer', question.textchoices.split(';'))
else:
r['textanswers'] = [a.textanswer for a in ConferenceFeedbackAnswer.objects.only('textanswer').filter(conference_id=confid, question_id=questionid).exclude(textanswer='')]
else:
# Numeric choices from 1-5
r['graphdata'] = build_graphdata(question, 'rateanswer', range(0, 6))
return r
def feedback_report(request, confname):
conference = get_authenticated_conference(request, confname)
sections = []
# Get the global conference feedback. Yes, this will be inefficient, but it will work
currentsection = {}
for q in ConferenceFeedbackQuestion.objects.filter(conference=conference).order_by('sortkey'):
if q.newfieldset:
if currentsection:
sections.append(currentsection)
currentsection = {}
if not currentsection:
# Either first row, or a new fieldset per above
currentsection['title'] = q.newfieldset
currentsection['questions'] = []
currentsection['questions'].append(build_feedback_response(q))
else:
sections.append(currentsection)
return render(request, 'confreg/admin_conference_feedback.html', {
'conference': conference,
'feedback': sections,
'helplink': 'feedback',
})
def build_toplists(what, query):
cursor = connection.cursor()
for k in ('topic_importance', 'content_quality', 'speaker_knowledge', 'speaker_quality'):
tl = {'title': '%s by %s' % (what, k.replace('_', ' ').title())}
cursor.execute(query.replace('{{key}}', k))
tl['list'] = cursor.fetchall()
yield tl
def feedback_sessions(request, confname):
conference = get_authenticated_conference(request, confname)
# Get all sessions that have actual comments on them
cursor = connection.cursor()
cursor.execute("SELECT s.title || ' (' || (SELECT string_agg(fullname, ', ') FROM confreg_speaker spk INNER JOIN confreg_conferencesession_speaker css ON css.speaker_id=spk.id WHERE css.conferencesession_id=s.id) || ')', conference_feedback FROM confreg_conferencesessionfeedback fb INNER JOIN confreg_conferencesession s ON fb.session_id=s.id WHERE s.conference_id=%s AND NOT conference_feedback='' ORDER BY 1,2" % (conference.id,))
commented_sessions = cursor.fetchall()
# Now for all of our fancy toplists
# The django ORM just can't do this...
minvotes = 10
if request.method == 'POST':
minvotes = int(request.POST['minvotes'])
toplists = []
# Start with top sessions
toplists.extend(build_toplists('Sessions', "SELECT s.title || ' (' || (SELECT string_agg(fullname, ', ') FROM confreg_speaker spk INNER JOIN confreg_conferencesession_speaker css ON css.speaker_id=spk.id WHERE css.conferencesession_id=s.id) || ')', avg(fb.{{key}}), count(*), stddev(fb.{{key}}) FROM confreg_conferencesessionfeedback fb INNER JOIN confreg_conferencesession s ON fb.session_id=s.id WHERE s.conference_id=%s AND fb.{{key}}>0 GROUP BY s.id HAVING count(*)>%s ORDER BY 2 DESC" % (conference.id, minvotes)))
# Now let's do the speakers
toplists.extend(build_toplists('Speakers', "SELECT (SELECT string_agg(fullname, ', ') FROM confreg_speaker spk INNER JOIN confreg_conferencesession_speaker css ON css.speaker_id=spk.id WHERE css.conferencesession_id=s.id) AS speakername, avg(fb.{{key}}), count(*), stddev(fb.{{key}}) FROM confreg_conferencesessionfeedback fb INNER JOIN confreg_conferencesession s ON fb.session_id=s.id WHERE s.conference_id=%s AND fb.{{key}}>0 GROUP BY speakername HAVING count(*)>%s ORDER BY 2 DESC" % (conference.id, minvotes)))
return render(request, 'confreg/admin_session_feedback.html', {
'conference': conference,
'toplists': toplists,
'minvotes': minvotes,
'commented_sessions': commented_sessions,
'breadcrumbs': (('/events/admin/{0}/reports/feedback/'.format(conference.urlname), 'Feedback'), ),
'helplink': 'feedback',
})
|
