def register_user(login: str, user_password: str):
from main import logger
db_session = Session()
users = db_session.query(Users).filter(Users.login == login).all()
if len(users) == 0:
logger.info(f'User signup with wrong e-mail: {login}')
return None
db_invites = db_session.query(Invites).all()
for db_invite in db_invites:
if check_password_hash(users[0].user_password, db_invite.invite_id):
invite_id = db_invite.invite_id
update_q = update(Users).where(
Users.login == login). \
values(user_password=generate_password_hash(
user_password,
"sha256",
salt_length=8))
db_session.execute(update_q)
db_session.commit()
db_session.close()
logger.info(f'User {login} successful registered '
f'with invite_id: {invite_id}')
return None
role = db_session.query(
RolesOfUsers.user_role).filter(RolesOfUsers.login == login).all()
if role[0][0] == 'deleted':
logger.info(f'User signup with e-mail: {login}, '
f'but this account deleted')
return None
db_session.close()
logger.info(f'User signup with e-mail: {login}, '
f'but this account already exists')
return 'Such account already exists'
def db_chat_create(observer_login: str, observer_pass: str,
target_profile_id: str) -> bytes:
# chat session require profiles in database
# check profiles in database
sender_profile = Profiles(profile_id=observer_login,
profile_password=observer_pass,
available=True,
can_receive=True)
db_session = Session()
# check if sender profile exists in database
profiles = db_session.query(Profiles).filter(
Profiles.profile_id == sender_profile.profile_id).all()
if len(profiles) == 0:
db_session.add(sender_profile)
db_session.commit()
# check receiver profile in database
profiles = db_session.query(Profiles).filter(
Profiles.profile_id == target_profile_id).all()
if len(profiles) == 0:
target_profile = Profiles(profile_id=target_profile_id,
can_receive=True,
available=True)
db_session.add(target_profile)
db_session.commit()
# finding chat id for this users
# find all chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == observer_login).subquery()
# find all chats with receiver
sub_query_2 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == target_profile_id).subquery()
# find chat from sender to receiver
query = db_session.query(ChatSessions). \
filter(ChatSessions.chat_id.in_(sub_query_1)). \
filter(ChatSessions.chat_id.in_(sub_query_2))
chat_sessions = query.all()
# if chat found, we return chat_id
if len(chat_sessions) != 0:
db_session.close()
return chat_sessions[0].chat_id
# if chat not found, we create chat and chat session
chat_id = uuid4().bytes
new_chat = Chats(chat_id=chat_id)
db_session.add(new_chat)
db_session.commit()
chat_session_1 = ChatSessions(chat_id=chat_id, profile_id=observer_login)
db_session.close()
db_session = Session()
db_session.add(chat_session_1)
db_session.commit()
chat_session_2 = ChatSessions(chat_id=chat_id,
profile_id=target_profile_id)
db_session.add(chat_session_2)
db_session.commit()
db_session.close()
return chat_id
def db_show_receivers(sender: str) -> list:
# you can swap sender and receiver
"""Returns list of dicts, with messages in dialogue for admin panel"""
db_session = Session()
# subquery for chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == sender).subquery()
# subquery for chats with this users
query = db_session.query(ChatSessions.profile_id). \
filter(ChatSessions.chat_id.in_(sub_query_1)). \
filter(ChatSessions.profile_id != sender)
# query to show messages and texts
receivers = query.all()
db_session.close()
return [{"profile_id": row[0]} for row in receivers]
def db_fill_visibility(login: str) -> bool:
"""Adds visibility status of all profiles for user by login value,
in database"""
db_session = Session()
query = db_session.query(Profiles.profile_id)
for profile in query.all():
query_check = db_session.query(Visibility.profile_id, Visibility.login)
query_check = query_check.filter(Visibility.login == login)
query_check = query_check.filter(Visibility.profile_id == profile[0])
check_result = query_check.all()
if len(check_result) > 0:
continue
access = Visibility(login=login, profile_id=profile[0])
db_session.add(access)
db_session.commit()
db_session.close()
return True
def db_chat_length_check(chat_id: bytes, total_msg: int,
sender_id: str) -> int:
# return count of new messages, which not presented in database
db_session = Session()
messages = db_session.query(Messages).filter(
Messages.chat_id == chat_id).filter(Messages.profile_id == sender_id)
db_session.close()
return total_msg - len(messages.all())
def db_get_rows(tables: list, *statements) -> list:
"""Select all rows from tables list,
which have been filtered with 'statements'"""
db_session = Session()
query = db_session.query(*tables)
for statement in statements:
query = query.filter(statement != '')
result = query.all()
db_session.close()
return result
def db_delete_rows(tables: list, *statements) -> int:
"""Delete all rows from tables list,
which have been filtered with 'statements'.
Returns number of deleted rows"""
db_session = Session()
query = db_session.query(*tables)
for statement in statements:
query = query.filter(statement != '')
rows = query.delete() # return number of deleted msg
db_session.commit() # return number of deleted msg
db_session.close()
return rows
def send_email_instruction(email_to):
"""Функция выполняет отправку сообщения на указанный email адресс с
инструкцией для регистрации в системе NatalyBot
Keyword arguments:
email_to -- адрессат, которому будет доставлено сообщение с инструкцией
"""
# Настройки по умолчанию
db_session = Session()
query = db_session.query(
EmailInfo.email_host, EmailInfo.email_port, EmailInfo.email_address,
EmailInfo.email_password, EmailInfo.email_subject,
EmailInfo.email_text).filter(
EmailInfo.email_description == 'default_register')
default_email_info = query.all()
if len(default_email_info) > 0:
HOST, PORT, EMAIL, PASSWORD, SUBJECT, TEXT = default_email_info[0]
db_session.close()
# Соединяемся с почтовым сервисом
smtpObj = smtplib.SMTP(HOST, PORT)
# Шифруем сообщение
smtpObj.starttls()
# Логинимся на почтовый ящик
smtpObj.login(EMAIL, PASSWORD)
# Экзепляр Email сообщения
message = EmailMessage()
# Текст сообщения
message.set_content(TEXT)
# Хедер сообщения. Нужен в соотвествии со стандартами Google
# Тема сообщения
message['Subject'] = SUBJECT
# Почта с которой идет отправка
message['From'] = EMAIL
# Кому отправляем
message['To'] = email_to
# Отправляем сообщение через наш почтовый сервер
smtpObj.send_message(message)
# Закрываем соединение с почтовым сервером
smtpObj.quit()
def db_get_profiles(*args) -> list:
db_session = Session()
query = db_session.query(Profiles.profile_id, Profiles.profile_password,
Profiles.available, Profiles.can_receive,
Profiles.msg_limit, Profiles.profile_type)
for statement in args:
query = query.filter(statement != '')
profiles = query.all()
profiles = [{
"profile_id": profile[0],
"profile_password": profile[1],
"available": profile[2],
"can_receive": profile[3],
"msg_limit": profile[4],
"profile_type": profile[5]
} for profile in profiles]
db_session.close()
return profiles
def db_get_users(*statements) -> list:
db_session = Session()
query = db_session.query(Users.login, Users.user_password,
SentInvites.invite_id, RolesOfUsers.user_role)
query = query.outerjoin(SentInvites, Users.login == SentInvites.login)
query = query.outerjoin(RolesOfUsers, Users.login == RolesOfUsers.login)
query = query.group_by(Users.login)
query = query.filter(Users.login != 'anonymous')
query = query.filter(Users.login != 'server')
query = query.filter(RolesOfUsers.user_role != 'deleted')
for statement in statements:
query = query.filter(statement != '')
users = query.all()
users = [{
"login": user[0],
"register_status": not check_password_hash(user[1], user[2]),
"role": user[3]
} for user in users]
db_session.close()
return users
def db_download_new_msg(observer_login: str, observer_password: str,
sender_id: str, receiver_profile_id: str) -> bool:
# find chat in db to delete new messages
chat_id = db_chat_create(observer_login=observer_login,
observer_pass=observer_password,
target_profile_id=receiver_profile_id)
db_session = Session()
msg_delete = db_session.query(Messages). \
filter(Messages.chat_id == chat_id). \
filter(Messages.viewed == False)
msg_delete.delete() # return number of deleted msg
db_session.commit()
db_session.close()
dialog_download(observer_login=observer_login,
observer_password=observer_password,
sender_id=sender_id,
receiver_profile_id=receiver_profile_id,
download_new=True)
dialog_download(observer_login=observer_login,
observer_password=observer_password,
sender_id=receiver_profile_id,
receiver_profile_id=receiver_profile_id,
download_new=True)
return True
def db_show_dialog(sender: str = None,
receiver: str = None,
email_filter: bool = False,
inbox_filter: bool = False,
outbox_filter: bool = False,
descending: bool = False) -> list:
# you can swap sender and receiver
"""Returns list of dicts, with messages in dialogue for admin panel"""
db_session = Session()
# Check visibility of profiles for current user
sub_query_0 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == Visibility.profile_id). \
filter(Visibility.login == current_user.login)
# subquery for chats with this profiles
query_chat = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.chat_id.in_(sub_query_0))
if sender:
# subquery for chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == sender).subquery()
# subquery for chats with this profiles
query_chat = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.chat_id.in_(sub_query_1))
if receiver:
# subquery for chats with receiver, if we have receiver id
sub_query_2 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == receiver).subquery()
# subquery for chats with this profiles
query_chat = query_chat. \
filter(ChatSessions.chat_id.in_(sub_query_2))
# filter only email dialogues
if email_filter:
query_chat = query_chat.filter(ChatSessions.email_address)
query_chat = query_chat.subquery()
# query to find accounts which have dialog
find_account_query = db_session.query(ChatSessions.profile_id). \
filter(ChatSessions.chat_id.in_(sub_query_0)). \
filter(Profiles.profile_id == ChatSessions.profile_id). \
filter(Profiles.profile_password != '').subquery()
# QUERY FOR MESSAGES
# query to show messages and texts
query = db_session.query(Messages.profile_id, Messages.send_time,
Messages.viewed, Texts.text,
ProfileDescription.nickname,
Messages.message_token)
query = query.filter(Messages.chat_id.in_(query_chat))
query = query.outerjoin(Texts, Messages.text_id == Texts.text_id)
query = query.filter(ProfileDescription.profile_id == Messages.profile_id)
if inbox_filter:
# show only inbox messages
query = query.filter(Messages.profile_id.notin_(find_account_query))
elif outbox_filter:
# show only inbox messages
query = query.filter(Messages.profile_id.in_(find_account_query))
# sorting
if descending:
query = query.order_by(desc(Messages.send_time))
else:
query = query.order_by(Messages.send_time)
# QUERY FOR ACCOUNTS
# query to show messages and texts
query_a = db_session.query(Messages.message_token)
query_a = query_a.filter(Messages.chat_id.in_(query_chat))
query_a = query_a.outerjoin(Texts, Messages.text_id == Texts.text_id)
query_a = query_a.filter(
ProfileDescription.profile_id == Messages.profile_id)
if inbox_filter:
# show only inbox messages
query_a = query_a.filter(
Messages.profile_id.notin_(find_account_query))
elif outbox_filter:
# show only inbox messages
query_a = query_a.filter(Messages.profile_id.in_(find_account_query))
query_a = query_a.order_by(Messages.send_time).subquery()
# final query to link messages with accounts
accounts = db_session.query(ProfileDescription.nickname,
ProfileDescription.profile_id,
Messages.message_token)
accounts = accounts. \
filter(ChatSessions.chat_id == Messages.chat_id). \
filter(ChatSessions.profile_id == Profiles.profile_id). \
filter(ProfileDescription.profile_id == Profiles.profile_id). \
filter(Profiles.profile_password != '')
accounts = accounts.join(query_a,
Messages.message_token == query_a.c.message_token)
result_accounts = accounts.all()
result = query.all()
db_session.close()
print(result)
print(result_accounts)
return [{
"profile_id": result[i][0],
"send_time": result[i][1],
"viewed": result[i][2],
"text": result[i][3],
"nickname": result[i][4],
"message_token": result[i][5],
"account_nickname": result_accounts[i][0],
"account_id": result_accounts[i][1]
} for i in range(len(result))]
def create_invite(creator: User, invited_email: str, role: str) -> bool:
from main import logger
db_session = Session()
invite = Invites(invite_id=uuid4().bytes)
# new user creating
new_user = Users(login=invited_email,
user_password=generate_password_hash(invite.invite_id,
"sha256",
salt_length=8))
# assign role to user
new_user_role = RolesOfUsers(login=invited_email, user_role=role)
# create invite from user
sent_invite_from = SentInvites(invite_id=invite.invite_id,
login=creator.login)
# create invite to user
sent_invite_to = SentInvites(invite_id=invite.invite_id,
login=new_user.login)
# database duplicate check
users = db_get_users(Users.login == invited_email)
if len(users) > 0:
if users[0]['role'] == 'deleted':
db_session = Session()
# create new invite id
db_session.add(invite)
db_session.commit()
# create new users-invite link
db_session.add(sent_invite_from)
db_session.add(sent_invite_to)
db_session.commit()
# change password from deleted to new, based on invite_id
update_q = update(Users).where(
Users.login == invited_email). \
values(user_password=new_user.user_password)
db_session.execute(update_q)
db_session.commit()
db_session.close()
# reload users, for next checks
users = db_get_users(Users.login == invited_email)
# user already created
if users[0]['register_status']:
# user already registered
logger.info(f'User {current_user.login} '
f'tried to create invite for '
f'already registered user: {invited_email}')
return False
if users[0]['role'] != role:
# user role another from db role
# check user role is valid
query = db_session.query(UserRoles).filter(
UserRoles.user_role == role)
if len(query.all()) == 0:
return False
update_q = update(RolesOfUsers).where(
RolesOfUsers.login == invited_email). \
values(user_role=role)
db_session.execute(update_q)
db_session.commit()
logger.info(f'User {current_user.login} '
f'update role for unregistered user: {invited_email}')
logger.info(f'User {current_user.login} '
f'resend invite to: {invited_email}')
return True
else:
# no user in DB
db_session.add(invite)
db_session.commit()
db_session.add(new_user)
db_session.commit()
db_session.add(new_user_role)
db_session.commit()
db_session.add(sent_invite_from)
db_session.add(sent_invite_to)
db_session.commit()
db_session.close()
logger.info(f'created invite for e-mail: {invited_email}')
return True