# -*- coding: utf-8 -*-
"""
The :mod:`reports` contains methods to generate various reports.
"""
import os
import cgi
from sqlalchemy import select,join,outerjoin, func, and_, or_, not_, desc, text
from sqlalchemy.types import INT
from ekorpus.lib.base import *
from ekorpus.models.form import *
from ekorpus.models.schema import \
session_table, test_table, segment_table, segmentation_table,\
recording_table,item_table, segment_table, response_table,\
choice_table, user_table, segmentscore_table, selection_table,\
groups_table, usergroups_table
[docs]class ReportsController(AuthorizedController):
[docs] def list(self):
"""List all reports."""
return render('/reports/list.mak')
#list.roles = 'admin'
[docs] def segments(self):
"""Generate a query form for selecting segmets with specified emotional properties."""
return render('/reports/segments.mak')
#list.roles = 'admin'
[docs] def segmentsresult(self):
"""Generate the report for the query issued by form for :meth:`segments`."""
p = request.params
emotion = p.get('emotion')
try:
level = int(p.get('level'))
except:
level =51
neutraltext = p.get('neutraltext')
pos = p.get('pos')
output = p.get('output')
if output:
if output=="1":
return self.wordsresult(emotion, level, neutraltext, pos);
elif output=="2":
return self.phonemesresult(emotion, level, neutraltext);
c.constraints={}
c.constraints['emotion']=emotion
c.constraints['level']=level
j=join(segmentscore_table,segment_table)
j=j.join(segmentation_table).join(recording_table)
q = select([segmentscore_table,segment_table,recording_table.c.comment],
and_(segmentscore_table.c.value>=level, segmentscore_table.c.code==emotion),
from_obj=[j],
order_by=[desc(segmentscore_table.c.value)],
engine=model.ctx.current.get_bind(None)
)
#add constraints
if neutraltext=='0':
q.append_whereclause(or_(
segmentscore_table.c.code!=segmentscore_table.c.text_code,
segmentscore_table.c.text_value<=50))
c.constraints['neutraltext']=_('no content influence')
elif neutraltext=='1':
q.append_whereclause(and_(
segmentscore_table.c.code==segmentscore_table.c.text_code,
segmentscore_table.c.text_value>50))
c.constraints['neutraltext']=_('content influences')
q = q.alias('scores')
c.scores = q.execute().fetchall()
return render('/reports/segmentsresult.mak')
[docs] def wordsresult(self, emotion, level, neutraltext, pos):
"""Generate the report for the query issued by form for :meth:`segments`."""
c.constraints={}
c.constraints['emotion']=emotion
c.constraints['level']=level
c.constraints['pos']=pos
nt = ""
if neutraltext=='0':
c.constraints['neutraltext']=_('no content influence')
nt = " and (sc.code!=sc.text_code or sc.text_value<=50) "
elif neutraltext=='1':
c.constraints['neutraltext']=_('content influences')
nt = " and (sc.code=sc.text_code or sc.text_value>50) "
t = text(
u"""
select w.id, w.lbl, se.code, se.value, se.text_code, se.text_value, w.jnr from
(
select sg1.recording_id, s1.id, s1.start, lower(s1.label) as lbl, s1.jnr
from segment s1 join segmentation sg1 on (s1.segmentation_id=sg1.id and sg1.type='word')
where not s1.label in (select pauses())
) w
join
(
select sg2.recording_id, s2.id, s2.start, s2.label
from segment s2 join segmentation sg2 on (sg2.id=s2.segmentation_id and sg2.type='pos')
where label like :pos
) w2
on (w.recording_id=w2.recording_id and w.start=w2.start)
join
(
select sg2.recording_id, s2.start, s2.end, sc.code, sc.value, sc.text_code, sc.text_value
from (segmentscore sc join segment s2 on sc.segment_id=s2.id) join segmentation sg2 on (s2.segmentation_id=sg2.id)
where sg2.type='sentence'
and sc.code=:emotion
and sc.value>=:level """
+nt+
""") se
on (w.start between se.start and se.end) and w.recording_id=se.recording_id
order by lbl, value
""", engine=model.ctx.current.get_bind(None))
c.scores = t.execute(level=level, emotion=emotion, pos=pos).fetchall()
return render('/reports/wordresult.mak')
[docs] def phonemesresult(self, emotion, level, neutraltext):
"""Generate the report for the query issued by form for :meth:`segments`."""
c.constraints={}
c.constraints['emotion']=emotion
c.constraints['level']=level
nt = ""
if neutraltext=='0':
c.constraints['neutraltext']=_('no content influence')
nt = " and (sc.code!=sc.text_code or sc.text_value<=50) "
elif neutraltext=='1':
c.constraints['neutraltext']=_('content influences')
nt = " and (sc.code=sc.text_code or sc.text_value>50) "
t = text(
u"""
select lbl, count(lbl), avg(len), coalesce(stddev(len),0) from (
select w.lbl, w.len from
(
select sg1.recording_id, s1.start, lower(s1.label) as lbl, s1.end-s1.start as len
from segment s1 join segmentation sg1 on (s1.segmentation_id=sg1.id and sg1.type='phoneme')
where not s1.label in (select pauses())
) w
join
(
select sg2.recording_id, s2.start, s2.end
from (segmentscore sc join segment s2 on sc.segment_id=s2.id) join segmentation sg2 on (s2.segmentation_id=sg2.id)
where sg2.type='sentence'
and sc.code=:emotion
and sc.value>=:level """
+nt+
""") se
on (w.start between se.start and se.end) and w.recording_id=se.recording_id
) r
group by lbl
order by lbl
""", engine=model.ctx.current.get_bind(None))
c.scores = t.execute(level=level, emotion=emotion).fetchall()
return render('/reports/phonemesresult.mak')
[docs] def scores(self):
"""Generate sentence segment performance report as CSV file for Emu."""
t = text(u"""
select
sc.segment_id as segment,
(select english from choice where code=sc.code and selection_id=7) as emotion,
sc.value as score,
(select english from choice where code=sc.text_code and selection_id=7) as text_emotion,
sc.text_value as text_score,
sg.recording_id as recording
from
segmentscore sc
join segment s on sc.segment_id=s.id
join segmentation sg on s.segmentation_id=sg.id
""", engine=model.ctx.current.get_bind(None))
#t = text(
#u"""
# select lower(s1.label) as label, s1.start, s1.end
# from segment s1
# where s1.label in (select pauses())
# and s1.start between :start and :end
# and s1.segmentation_id = (select id from segmentation where recording_id=:recording and type='word')
# order by start
#""", engine=model.ctx.current.get_bind(None))
res = t.execute(**request.params.mixed())
c.colnames = res.keys
c.rows = res.fetchall()
response.content_type = 'text/plain'
return render('/reports/csvresult.mak')
def _updateempathy(self):
sel=select(
["cast(substring(session.score,8) as integer)"],
and_(session_table.c.test_id==1,session_table.c.user_id==user_table.c.id)
)
upd=user_table.update(values={user_table.c.empathy:sel})
model.ctx.current.get_bind(None).execute(upd)
[docs] def tests(self):
"""Generate a query form for the tests."""
"""List all tests."""
c.tests=model.Test.select(order_by=['comment'])
c.groups=model.Groups.select()
return render('/reports/tests.mak')
#list.roles = 'admin'
[docs] def testsresult(self, id, xml=None):
"""Generates a report for a test *id* where for each item the performance
of each is listed.
"""
p = request.params
if not id:
id = p.get('test')
c.test = model.Test.get_by(id=id)
if 'empathy' in p:
self._updateempathy()
groups=None
if 'groups[]' in p:
groups = request.params.getall('groups[]')
#-prepare group_by selection--------------------------------------------
#j=join(user_table,session_table).join(response_table)
j=join(user_table,session_table)
if groups:
j=j.join(usergroups_table).join(groups_table)
j=j.join(response_table)
q = select([
response_table.c.item_id,
response_table.c.code,
func.count(response_table.c.code).label('count')
],
and_(user_table.c.ignore==0, session_table.c.test_id==id),
from_obj=[j],
group_by=[response_table.c.item_id,
response_table.c.code]
)
#-add constraints-------------------------------------------------------
c.constraints={}
if groups:
q.append_whereclause(groups_table.c.id.in_(*groups))
c.constraints['groups']=model.Groups.filter(groups_table.c.id.in_(*groups)).all()
#q.append_whereclause(groups_table.c.id==group.id)
#c.constraints['group']=group
if 'empathy' in p:
if p['empathy']=="1":
q.append_whereclause(user_table.c.empathy<30)
else:
q.append_whereclause(user_table.c.empathy>29)
c.constraints['empathy']=p['empathy']
if 'gender' in p:
q.append_whereclause(user_table.c.gender==p['gender'])
c.constraints['gender']=p['gender']
if 'age' in p:
if p['age']=='old':
q.append_whereclause(user_table.c.age>=30)
else:
q.append_whereclause(user_table.c.age<30)
c.constraints['age']=p['age']
q = q.alias('responses')
print '\n\n\n',q #FIXME remove
#TODO add experience constraint
#-append additional info------------------------------------------------
jj=join(item_table, q, item_table.c.id==q.c.item_id).\
join(selection_table, selection_table.c.id==item_table.c.selection_id).\
join(choice_table, and_(choice_table.c.selection_id==selection_table.c.id, choice_table.c.code==q.c.code)).\
outerjoin(segment_table,item_table.c.first_id==segment_table.c.id).\
outerjoin(segmentation_table).\
outerjoin(recording_table, segmentation_table.c.recording_id==recording_table.c.id)
qq = select([
item_table.c.id,
item_table.c.nr,
item_table.c.text,
recording_table.c.id.label('rid'),
segment_table.c.id.label('sid'),
segment_table.c.start,
segment_table.c.label,
choice_table.c.nr.label('cnr'),
q.c.code,
q.c.count
],
from_obj=[jj],
order_by=[item_table.c.nr,choice_table.c.nr],
engine=model.ctx.current.get_bind(None)
)
rows = qq.execute().fetchall()
#-fetch additional info ------------------------------------------------
recordings = set()
for r in rows:
if r.rid:
recordings.add(r.rid)
recordings = model.Recording.select(recording_table.c.id.in_(*recordings))
c.recnames = dict((r.id,r.comment) for r in recordings)
#c.codenames = model.Choice.select(choice_table.c.selection_id==c.test.selection_id, order_by=choice_table.c.nr)
#c.codenames = [r.text for r in c.codenames]
t = text(u'select coalesce(english,"text") from choice where selection_id=:selection_id order by nr',engine=model.ctx.current.get_bind(None))
c.codenames = t.execute(selection_id=c.test.selection_id).fetchall()
c.codenames = [r[0] for r in c.codenames]
counts = {}
segnames = {}
for r in rows:
if not (r.nr in counts):
counts[r.nr]=[0]*len(c.codenames)
counts[r.nr][-1+r.cnr]=r.count
if r.rid:
rlabel = r.label
if len(rlabel)>2 and rlabel[1]==',':
rlabel = rlabel[2:]
segnames[r.nr] = (model.show_start(r.start),rlabel+' / '+c.recnames[r.rid],r.sid)
else:
segnames[r.nr]=r.text
c.counts = counts
c.segnames = segnames
if xml:
response = render('/reports/testsx.mak')
#FIXME# response.headers['content-type'] = 'text/xml'
return response
return render('/reports/testsresult.mak')
#SELECT
# item.id, item.nr, item.text,
# recording.id AS rid,
# segment.id AS sid, segment.start, segment.label,
# choice.nr,
# responses.code,
# responses.count
#FROM
# item
# JOIN (
# SELECT
# response.item_id AS item_id, response.code AS code, count(response.code) AS count
# FROM
# "user"
# JOIN session ON "user".id = session.user_id
# JOIN response ON session.id = response.session_id
# WHERE "user".ignore = 0
# AND session.test_id = 4
# GROUP BY response.item_id, response.code
# ) AS responses ON item.id = responses.item_id
# JOIN selection ON selection.id = item.selection_id
# JOIN choice ON choice.selection_id = selection.id AND choice.code = responses.code
# LEFT OUTER JOIN segment ON item.first_id = segment.id
# LEFT OUTER JOIN segmentation ON segmentation.id = segment.segmentation_id
# LEFT OUTER JOIN recording ON segmentation.recording_id = recording.id
# ORDER BY item.nr, choice.nr
[docs] def valence(self):
"""Generate a query form for selecting segmets with specified valence properties."""
return render('/reports/valence.mak')
#list.roles = 'admin'
[docs] def valenceresult(self):
"""Generate the report for the sentence valence recognition."""
p = request.params
valence = p.get('valence')
try:
level = int(p.get('level'))
except:
level =51
neutraltext = p.get('neutraltext')
emotion = p.get('emotion')
c.constraints={}
c.constraints['valence']=valence
c.constraints['level']=level
nt = ''
if neutraltext=='0':
c.constraints['neutraltext']=_('no content influence')
nt = " and (sc.code!=sc.text_code or sc.text_value<=50) "
elif neutraltext=='1':
c.constraints['neutraltext']=_('content influences')
nt = " and (sc.code=sc.text_code or sc.text_value>50) "
emotionjoin = ''
emotioncond = ''
if emotion:
c.constraints['emotion']=emotion
emotionjoin = "join segmentscore esc on sc.segment_id = esc.segment_id "
emotioncond = "and esc.code=:emotion and esc.value>50 "
q =u"""
select s.id, s.label, s.start, sc.code, sc.value, sc.text_code, sc.text_value, r.comment
from
valencescore sc
join segment s on sc.segment_id = s.id
join segmentation sg on s.segmentation_id = sg.id
join recording r on sg.recording_id = r.id
"""+emotionjoin+"""
where
sc.code=:valence
and sc.value>=:level
"""+nt+emotioncond+"order by value desc"
t = text(q, engine=model.ctx.current.get_bind(None))
c.scores = t.execute(level=level, valence=valence, emotion=emotion).fetchall()
return render('/reports/valenceresult.mak')