def query_latest_conversations(limit):
query = """
SELECT
m.conversation_id AS id,
MAX(m.id) as msg_id,
MAX(m.timestamp) AS timestamp,
c.user_id AS user_id,
m.localim AS localim,
m.remoteim AS remoteim,
count(m.id) as total
FROM conversations as c
LEFT OUTER JOIN messages as m ON m.conversation_id = c.id
WHERE
YEAR(m.timestamp) = %(year)i
AND MONTH(m.timestamp) = %(month)i
AND DAYOFMONTH(m.timestamp) = %(day)i
GROUP BY c.id
ORDER BY m.timestamp DESC
LIMIT 0, %(limit)i
"""
localdate = datetime.now().timetuple()
query = query % {
'year': localdate.tm_year,
'month': localdate.tm_mon,
'day': localdate.tm_mday,
'limit': limit
}
#print query
result = fetchall_to_dict(query)
return [DataTree.LatestConversation(r) for r in result]
def query_most_active_users(limit, period, initial_localdate=datetime.now()):
period_cond = {
'day': lambda x: """
YEAR(timestamp) = %(year)i
AND MONTH(timestamp) = %(month)i
AND DAYOFMONTH(timestamp) = %(day)i
""",
'week': lambda x: "WEEKOFYEAR(timestamp) = %(week)i",
'month': lambda x: """
YEAR(timestamp) = %(year)i
AND MONTH(timestamp) = %(month)i
""",
'year': lambda x: "YEAR(timestamp) = %(year)i",
}.get(period, lambda x: None)(period)
if not period_cond:
return []
query = """
SELECT
u.id AS user_id,
m.localim AS username,
count(m.id) AS total,
m.timestamp AS timestamp
FROM messages as m
LEFT OUTER JOIN users as u ON u.username = m.localim
WHERE
m.inbound = 0 AND
""" + period_cond + """
GROUP BY user_id
ORDER BY total DESC
LIMIT 0, %(limit)i
"""
weekofyear = initial_localdate.isocalendar()[1]
localdate = initial_localdate.timetuple()
query = query % {
'year': localdate.tm_year,
'month': localdate.tm_mon,
'week': weekofyear,
'day': localdate.tm_mday,
'limit': limit
}
#print query
result = fetchall_to_dict(query)
return [DataTree.MostActiveUser(r) for r in result]