Beispiel #1
0
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]
Beispiel #2
0
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]