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_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 the_wall():
messages = []
# Grab all messages and the users that created them
tuple_messages = db.execute_sql(
"SELECT message.id, user.first_name, user.last_name, message.created_at, \
message.content FROM message JOIN user ON message.user_id = user.id \
ORDER BY message.created_at DESC;")
# With each message's tuple create a dictionary of key val pairs
for message in tuple_messages:
message_info = "{} {} - {}".format(
message[1], message[2], message[3].strftime("%B %d %Y"))
message_dict = {
"message_id": message[0],
"message_info": message_info,
"message_content": message[4],
"message_deleteable": False,
"comment_list": []
}
# Change "message_deletable value if message is not older that 30min"
if message[3] > datetime.datetime.now() - datetime.timedelta(minutes=30):
message_dict["message_deleteable"] = True
# Grab all the comments made on message and their creators
tuple_comments = db.execute_sql(
"SELECT comment.id, user.first_name, user.last_name, \
comment.message_id, comment.content, comment.created_at FROM \
comment JOIN user ON user.id = comment.user_id \
WHERE comment.message_id = {};".format(message[0])
)
# With each comment's tuple create a dict of key val pairs
for comment in tuple_comments:
comment_info = "{} {} - {}".format(
comment[1], comment[2], comment[5].strftime("%B %d %Y"))
comment_dict = {
"comment_id": comment[0],
"comment_info": comment_info,
"comment_content": comment[4]
}
message_dict["comment_list"].append(comment_dict)
# add each message dict to the messages list
messages.append(message_dict)
return render_template("the_wall.html", messages=messages)
def the_wall():
messages = []
# Grab all messages and the users that created them
tuple_messages = db.execute_sql(
"SELECT message.id, user.first_name, user.last_name, message.created_at, \
message.content FROM message JOIN user ON message.user_id = user.id \
ORDER BY message.created_at DESC;")
# With each message's tuple create a dictionary of key val pairs
for message in tuple_messages:
message_info = "{} {} - {}".format(message[1], message[2],
message[3].strftime("%B %d %Y"))
message_dict = {
"message_id": message[0],
"message_info": message_info,
"message_content": message[4],
"message_deleteable": False,
"comment_list": []
}
# Change "message_deletable value if message is not older that 30min"
if message[3] > datetime.datetime.now() - datetime.timedelta(
minutes=30):
message_dict["message_deleteable"] = True
# Grab all the comments made on message and their creators
tuple_comments = db.execute_sql(
"SELECT comment.id, user.first_name, user.last_name, \
comment.message_id, comment.content, comment.created_at FROM \
comment JOIN user ON user.id = comment.user_id \
WHERE comment.message_id = {};".format(message[0]))
# With each comment's tuple create a dict of key val pairs
for comment in tuple_comments:
comment_info = "{} {} - {}".format(comment[1], comment[2],
comment[5].strftime("%B %d %Y"))
comment_dict = {
"comment_id": comment[0],
"comment_info": comment_info,
"comment_content": comment[4]
}
message_dict["comment_list"].append(comment_dict)
# add each message dict to the messages list
messages.append(message_dict)
return render_template("the_wall.html", messages=messages)
def load(fp):
reader = csv.reader(fp)
reader.next() # Throw out the column headers
with db.transaction():
for row in reader:
crime = Crime(
district = row[0],
sector = row[1],
dispatch_time = row[2],
location = row[7],
code = row[8],
description = row[10],
lon = row[12],
lat = row[13])
crime.save(force_insert = True)
db.execute_sql("""
UPDATE crime SET geom = ST_GeomFromText(
'POINT(' || lon || ' ' || lat || ')', 4326
);""")
def main():
opts = get_options()
if os.path.exists(opts.name):
if opts.force_overwrite:
os.remove(opts.name)
else:
sys.exit(f'File {opts.name} exists, use --force to overwrite')
initialize_database(db, opts.name)
db.create_tables(
[PeeweeIndex4S, PeeweeIndex4U, PeeweeIndex5S, PeeweeIndex5U])
for statement in SQL_TABLE_STATEMENTS.values():
db.execute_sql(statement)
for fn in os.listdir(opts.corpus_dir):
path = os.path.join(opts.corpus_dir, fn)
with open(path) as fp:
text = fp.read()
for cls in [
PeeweeIndex4S, PeeweeIndex4U, PeeweeIndex5S, PeeweeIndex5U
]:
cls.insert({'text': text, 'source': fn}).execute()
for tbl_name in ['di_4_s', 'di_4_u', 'di_5_s', 'di_5_u']:
sql = f'insert into {tbl_name} (text, source) values (?, ?)'
db.execute_sql(sql, (text, fn))
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_delta(value, price_type, ticker_code):
query = ("SELECT MAX( select1.date ) as date1, date2 FROM ( \n"
"SELECT t1.date, t1.{type}, \n"
"MIN(t2.date) as date2 \n"
"FROM price t1 \n"
"INNER JOIN price t2 \n"
"ON (t2.{type} >= t1.{type} + {value})\n"
"AND t1.date < t2.date\n"
"AND t1.stock_id = t2.stock_id\n"
"WHERE t1.stock_id = '{ticker_code}'\n"
"GROUP BY t1.id\n"
"ORDER BY t1.date ) select1\n"
"GROUP BY date2;").format(type=price_type,
ticker_code=ticker_code,
value=value)
cursor = db.execute_sql(query)
dates = [list(x) for x in cursor]
print(dates)
return dates
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))
elif re.match(r'(?=.*[0-9])([a-z0-9]+)',passwd):
if len(passwd) >= 8:
complexity = 3
else:
complexity = 2
elif re.match(r'(?=.*[0-9])(?=.*[A-Z])([a-zA-Z0-9]+)',passwd):
complexity = 3
elif re.match(r'(?=.*[0-9])(?=.*[A-Z])(?=.*[!"#$%&\'()*+,-./:;<=>?@\[\\\]^_`{|}~])([a-zA-Z0-9!"#$%&\'()*+,-./:;<=>?@\[\\\]^_`{|}~]+)',passwd):
complexity = 4
def create_user(dbfile):
conn = db.connect_db(dbfile)
db.create_user_table(conn)
sql = "insert into users (name,email,passwd) values ('{}','{}','{}');"
detail = get_user()
pwd = generate_password(random.randint(6,12),random.randint(1,4))
sql = sql.format(detail.get('fullname'),detail.get('email'),pwd)
db.execute_insert_sql(sql,conn)
conn.close()
if __name__ == '__main__':
usercount = int(input("Enter no. of users to create :"))
cur_dir= os.getcwd()
db_file = os.path.join(cur_dir,'users.db')
for i in range(0,usercount):
create_user(db_file)
con = db.connect_db(db_file)
print(db.execute_sql('select * from users',con))
con.close()
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))