def dialogue_depths(self, site_id, group_id, start_period, end_period):
p = self.postTable
s = sa.select([sa.func.count(p.c.topic_id)], group_by=p.c.topic_id)
s.append_whereclause(p.c.site_id == site_id)
s.append_whereclause(p.c.group_id == group_id)
s.append_whereclause(p.c.date >= start_period)
s.append_whereclause(p.c.date <= end_period)
session = getSession()
r = session.execute(s)
count = r.rowcount
depths = []
if count:
for row in r:
depths.append(row[0])
total = sum(depths)
avgdepth = 0
mindepth = 0
maxdepth = 0
if count:
avgdepth = float(total) / float(count)
mindepth = min(depths)
maxdepth = max(depths)
return mindepth, maxdepth, avgdepth
def joinleave_counts(self, site_id, group_id, start_period, end_period):
aet = self.auditEventTable
start_period = dt_to_user_timezone(self.context, start_period,
self.user)
end_period = dt_to_user_timezone(self.context, end_period, self.user)
s = sa.select([
aet.c.subsystem,
sa.func.count(aet.c.subsystem),
],
group_by=aet.c.subsystem)
joinClauses = ((aet.c.subsystem == JOIN_SUBSYSTEM)
& (aet.c.event_code == JOIN))
leaveClauses = ((aet.c.subsystem == LEAVE_SUBSYSTEM)
& (aet.c.event_code == LEAVE))
s.append_whereclause(joinClauses | leaveClauses)
s.append_whereclause(aet.c.group_id == group_id)
s.append_whereclause(aet.c.site_id == site_id)
s.append_whereclause(aet.c.event_date >= start_period)
s.append_whereclause(aet.c.event_date <= end_period)
session = getSession()
r = session.execute(s)
retval = {'gs.group.member.join': 0, 'gs.group.member.leave': 0}
for result in r:
subsystem, count = result
assert subsystem in ('gs.group.member.join',
'gs.group.member.leave')
retval[subsystem] = count
# {'gs.group.member.join': COUNT, 'gs.group.member.leave': COUNT}
return retval
def authors(self, site_id, group_id, start_period, end_period):
t = self.postTable
s = sa.select([t.c.user_id], distinct=True)
s.append_whereclause(t.c.site_id == site_id)
s.append_whereclause(t.c.group_id == group_id)
s.append_whereclause(t.c.date >= start_period)
s.append_whereclause(t.c.date <= end_period)
session = getSession()
r = session.execute(s)
retval = r.rowcount
return retval
def active_topics(self, site_id, group_id, start_period, end_period):
p = self.postTable
s = sa.select([p.c.topic_id], distinct=True, group_by=p.c.topic_id)
s.append_whereclause(p.c.site_id == site_id)
s.append_whereclause(p.c.group_id == group_id)
s.append_whereclause(p.c.date >= start_period)
s.append_whereclause(p.c.date <= end_period)
session = getSession()
r = session.execute(s)
retval = r.rowcount
return retval
def members_on_digest(self, site_id, group_id):
t = self.emailSettingTable
s = t.select()
#s.append_whereclause(t.c.site_id==site_id)
s.append_whereclause(t.c.group_id == group_id)
s.append_whereclause(t.c.setting == 'digest')
session = getSession()
r = session.execute(s)
retval = r.rowcount
return retval
def posts_by_web(self, site_id, group_id, start_period, end_period):
t = self.auditEventTable
s = sa.select([t.c.user_id], distinct=True)
s.append_whereclause(t.c.site_id == site_id)
s.append_whereclause(t.c.group_id == group_id)
s.append_whereclause(t.c.event_date >= start_period)
s.append_whereclause(t.c.event_date <= end_period)
s.append_whereclause(t.c.subsystem == u'groupserver.WebPost')
session = getSession()
r = session.execute(s)
retval = r.rowcount
return retval
def posts(self, site_id, group_id, start_period, end_period):
t = self.postTable
s = t.select()
s.append_whereclause(t.c.site_id == site_id)
s.append_whereclause(t.c.group_id == group_id)
s.append_whereclause(t.c.date >= start_period)
s.append_whereclause(t.c.date <= end_period)
session = getSession()
r = session.execute(s)
retval = r.rowcount
return retval
def new_topics(self, site_id, group_id, start_period, end_period):
s = sa.text("""select min_date from
(select min(date) as min_date from post
where group_id=:group_id and
site_id=:site_id
group by topic_id) as min_topic
where min_topic.min_date>=:start_period and
min_topic.min_date<=:end_period""")
session = getSession()
r = session.execute(s,
params={
'site_id': site_id,
'group_id': group_id,
'start_period': start_period,
'end_period': end_period
})
retval = r.rowcount
return retval
def earliest_member_record(self, site_id, group_id):
aet = self.auditEventTable
s = sa.select([
sa.func.min(aet.c.event_date),
])
joinClauses = ((aet.c.subsystem == JOIN_SUBSYSTEM)
& (aet.c.event_code == JOIN))
leaveClauses = ((aet.c.subsystem == LEAVE_SUBSYSTEM)
& (aet.c.event_code == LEAVE))
s.append_whereclause(joinClauses | leaveClauses)
s.append_whereclause(aet.c.group_id == group_id)
s.append_whereclause(aet.c.site_id == site_id)
session = getSession()
r = session.execute(s)
if r.rowcount:
return r.fetchone()[0]
return None
