def get(self, conversation_id=None):
calls = db.execute_sql("select * from `call` where conversation_id='{}';".format(conversation_id)).fetchall()
output = {
"total_duration_sec": 0,
"nb_call": 0,
"nb_call_missed": 0,
"participants": "",
"total_duration_pretty": "",
"average_duration_pretty": "",
"average_duration_sec": ""
}
if not calls:
return messages.message(output, namespace=self.get_namespace(request))
participants = {}
for call in calls:
output["total_duration_sec"] += call[8]
if call[9] == 1:
output["nb_call_missed"] += 1
else:
output["nb_call"] += 1
if call[1] not in participants:
participants[call[1]] = {
"nb_call": 0
}
participants[call[1]]["nb_call"] += 1
output["total_duration_pretty"] = format_duration(output["total_duration_sec"])
try:
output["average_duration_sec"] = int(output["total_duration_sec"] / output["nb_call"])
except ZeroDivisionError:
output["average_duration_sec"] = 0
output["average_duration_pretty"] = format_duration(output["average_duration_sec"])
output["participants"] = format_participants(participants, output["nb_call"])
return messages.message(output, namespace=self.get_namespace(request))
def get_messages_per_day(self, conversation_id):
messages_per_day = db.execute_sql("""
SELECT
DAYOFWEEK(sent_at) AS d,
sender,
COUNT(*)
FROM message
WHERE conversation_id='{}'
GROUP BY d, sender;
""".format(conversation_id)).fetchall()
# Load in dict
messages_per_day_per_user = {}
for message in messages_per_day:
if message[1] not in messages_per_day_per_user:
messages_per_day_per_user[message[1]] = {
"color":
get_conf("colors")[len(messages_per_day_per_user)],
"data": []
}
messages_per_day_per_user[message[1]]["data"].append({
"x":
get_conf("days_of_week")[int(message[0])],
"y":
message[2]
})
# Build output
output = []
for name, value in messages_per_day_per_user.items():
# Put sunday at the end of the list
value["data"] = value["data"][1:] + [value["data"][0]]
value.update({"title": name})
output.append(value)
return messages.message(output, namespace=self.get_namespace(request))
def get_events(self, conversation_id):
# Get all message with type "Subscribe" or "Unsubscribe"
messages_per_day = Message.select() \
.where(Message.conversation_id == conversation_id) \
.where(Message.type.in_(["Subscribe", "Unsubscribe"])) \
.order_by(Message.sent_at.asc())
# Format output (clean content and add change +1 or -1)
output = []
for message in messages_per_day:
tmp = {
"sent_at":
message.sent_at.strftime("%b %d %Y"),
"content":
decode_str(message.content).replace(
" to the group.",
"").replace(" from the group.",
"").replace(" the group.", ""),
"sender":
message.sender
}
if "added" in tmp["content"]:
tmp["content"] = "{} added{}".format(
tmp["sender"], tmp["content"].split("added")[1])
tmp["change"] = "+1"
elif "removed" in tmp["content"]:
tmp["content"] = "{} removed{}".format(
tmp["sender"], tmp["content"].split("removed")[1])
tmp["change"] = "-1"
elif "left" in tmp["content"]:
tmp["change"] = "-1"
output.append(tmp)
return messages.message(output, namespace=self.get_namespace(request))
def get_emojies(self, conversation_id):
nb_emojis = {}
# Get all message with a content
messages_ = Message.select() \
.where(Message.content != None) \
.where(Message.content != "") \
.where(Message.type == "Generic") \
.where(Message.conversation_id == conversation_id)
log.info("{} messages found".format(len(messages_)))
# Group emojies by users
for message in messages_:
try:
tmp = message.content.encode('latin1').decode('utf8')
except:
continue
for char in tmp:
if char in emoji.UNICODE_EMOJI:
if message.sender not in nb_emojis:
nb_emojis[message.sender] = defaultdict(int)
nb_emojis[message.sender][char] += 1
# Format output and keep only the top 10
output = []
for sender, value in nb_emojis.items():
tmp = {"sender": sender, "emoji": []}
for emoji_, count in value.items():
tmp["emoji"].append({'emoji': emoji_, "nb_messages": count})
tmp["emoji"] = sorted(tmp["emoji"],
key=lambda i: i['nb_messages'],
reverse=True)[:10]
output.append(tmp)
return messages.message(output, namespace=self.get_namespace(request))
def get_language(self, conversation_id):
"""
Keep only message with > 20 char and 5 words
Get language of each messages
Keep 4 most used languages
Add others value's = sum of all others languages
"""
message_per_lang = defaultdict(int)
messages_ = db.execute_sql("""
SELECT content
FROM message
WHERE
conversation_id='{}' AND
content IS NOT NULL AND
content <> "" AND
CHARACTER_LENGTH(content) > 20
""".format(conversation_id)).fetchall()
log.info("{} messages found".format(len(messages_)))
messages_ = [x[0] for x in messages_ if len(x[0].split(' ')) >= 5]
log.info("{} messages with words".format(len(messages_)))
for content in messages_:
try:
content = content.encode('latin1', 'ignore').decode('utf8')
except:
continue
lang = langid.classify(content)[0]
message_per_lang[lang] += 1
# Build output
output = []
for lang, nb in message_per_lang.items():
output.append({
"lang":
lang,
"language_pretty":
pycountry.languages.get(alpha_2=lang).name,
"nb_messages":
nb,
"flag":
get_conf("flags").get(lang,
get_conf("flags").get("other"))
})
output = sorted(output, key=lambda i: i['nb_messages'], reverse=True)
# Merge "other" language
others = sum([x["nb_messages"] for x in output[4:]])
# Keep top 5
output = output[:4]
output.append({
"lang": "others",
"language_pretty": "Others",
"nb_messages": others,
"flag": get_conf("flags")["other"]
})
return messages.message(output, namespace=self.get_namespace(request))
def get(self, conversation_id=None):
output = []
for conversation in Conversation.select().order_by(Conversation.count_messages.desc()):
if conversation.title == "":
continue
output.append({
"title": conversation.title,
"nb_messages": str(conversation.count_messages),
"is_still_participant": conversation.is_still_participant,
"conversation_id": conversation.conversation_id
})
return messages.message(output, namespace=self.get_namespace(request))
def get_messages_per_hour(self, conversation_id):
output = {"messages_per_hour": []}
messages_per_hour = db.execute_sql("""
SELECT
HOUR(sent_at) AS h,
COUNT(*)
FROM message
WHERE
conversation_id='{}'
GROUP BY h
ORDER BY h;
""".format(conversation_id)).fetchall()
for message in messages_per_hour:
output["messages_per_hour"].append({
"x": "{}h".format(message[0]),
"y": message[1]
})
return messages.message(output, namespace=self.get_namespace(request))
def get_messages_per_month(self, conversation_id):
output = {"messages_per_month": []}
messages_per_month = db.execute_sql("""
SELECT
DATE_FORMAT(sent_at, '%%Y-%%m') AS y,
COUNT(*)
FROM message
WHERE conversation_id='{}'
GROUP BY y
ORDER BY y ASC;
""".format(conversation_id)).fetchall()
for message in messages_per_month:
output["messages_per_month"].append({
"x":
"{month}/01/{year}".format(month=message[0].split('-')[1],
year=message[0].split('-')[0]),
"y":
message[1]
})
return messages.message(output, namespace=self.get_namespace(request))
def get_content(self, conversation_id):
content = db.execute_sql("""
SELECT
COUNT(photos),
COUNT(video),
COUNT(share),
COUNT(sticker),
COUNT(gifs),
COUNT(audio)
FROM message
WHERE conversation_id='{}'
""".format(conversation_id)).fetchall()[0]
return messages.message(
{
"photos": content[0],
"videos": content[1],
"shares": content[2],
"stickers": content[3],
"gifs": content[4],
"audios": content[5]
},
namespace=self.get_namespace(request))
def get_conversation_info(self, conversation_id):
conversations = db.execute_sql("""
SELECT
COUNT(*),
MAX(sent_at),
MIN(sent_at),
sender,
ANY_VALUE(is_still_participant),
ANY_VALUE(title),
ANY_VALUE(thread_type),
SUM(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1)
FROM message
WHERE conversation_id='{}'
GROUP BY sender;
""".format(conversation_id)).fetchall()
# Build output
output = {
"nb_messages_per_user": [],
"nb_messages":
sum([x[0] for x in conversations]),
"title":
conversations[0][5],
"is_group_conversation":
True if conversations[0][6] == "RegularGroup" else False,
"is_still_participant":
bool(conversations[0][4]),
"first_message":
min([x[2] for x in conversations]).strftime("%b %d %Y %H:%M:%S"),
"last_message":
max([x[1] for x in conversations]).strftime("%b %d %Y %H:%M:%S"),
"nb_words":
sum([int(x[7]) for x in conversations]),
}
output["words_per_message"] = round(
output["nb_words"] / output["nb_messages"], 2)
# Calculate messages per day
try:
output["message_per_day"] = round(
output["nb_messages"] / (parse(output["last_message"]) -
parse(output["first_message"])).days,
2)
except ZeroDivisionError:
output["message_per_day"] = 0.0
# Add participants, nb messages/participants, sort list
for i, conversation in enumerate(conversations):
output["nb_messages_per_user"].append({
"user":
conversation[3],
"nb_message":
conversation[0],
"label":
conversation[3],
"color":
get_conf("colors")[i],
"rate":
round(conversation[0] * 100 / output["nb_messages"], 2),
"words":
int(conversation[7]),
"time_spent":
format_duration(int(conversation[7]) * 1.4)
})
output["nb_messages_per_user"] = sorted(output["nb_messages_per_user"],
key=lambda i: i['nb_message'],
reverse=True)
return messages.message(output, namespace=self.get_namespace(request))