def get_all_waiting_requests():
check_authentication()
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_query = """SELECT waiting_id FROM waiting_list;"""
db_all_requests = sqlite_connection.execute(select_query)
counter = 0
for req in db_all_requests:
counter += 1
sqlite_connection.close()
return counter
def outbox_to_trash(mail_id):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
check_query = """SELECT multiple_receivers FROM mail WHERE mail_id = ?;"""
db_check_result = sqlite_connection.execute(check_query, (mail_id,))
check_multiple = False
for check in db_check_result:
check_multiple = check[0]
break
if check_multiple:
select_mail_date = """SELECT mail_date FROM mail WHERE mail_id = ?;"""
db_mail_date = sqlite_connection.execute(select_mail_date, (mail_id,))
mail_date = 0
for date in db_mail_date:
mail_date = date[0]
break
select_sender_all_mails = """SELECT mail_id FROM mail WHERE sender_id = ? AND mail_date = ?;"""
db_no_of_mails = sqlite_connection.execute(
select_sender_all_mails, (get_user_id(session["email"]), mail_date)).fetchall()
update_mail_to_trash_query = """UPDATE mail SET sender_trashed = ? WHERE sender_id = ? AND mail_date = ?;"""
for i in range(len(db_no_of_mails)):
sqlite_connection.execute(
update_mail_to_trash_query, (1, get_user_id(session["email"]), mail_date))
else:
update_mail_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;"""
sqlite_connection.execute(update_mail_query, (1, mail_id))
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for("user_mail_bp.see_outbox"))
def admin_page():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
check_unauthorized_query = """SELECT user_email FROM unauthorize;"""
db_unauthorized_mails = sqlite_connection.execute(check_unauthorized_query)
un_authorized_mails = []
for mail in db_unauthorized_mails:
un_authorized_mails.append(mail[0])
select_all_users = """SELECT user_id, user_name, user_email, user_active FROM user;"""
db_all_users = sqlite_connection.execute(select_all_users)
user_data = {}
all_users = []
for user in db_all_users:
user_data["user_id"] = user[0]
user_data["user_name"] = user[1]
user_data["user_email"] = user[2]
user_data["user_status"] = user[3]
user_data["user_authorized"] = True
for mail in un_authorized_mails:
if mail == user[2]:
user_data["user_authorized"] = False
break
all_users.append(user_data)
user_data = {}
waiting_requests = get_all_waiting_requests()
return render_template("admin_templates/admin.html",
all_users=all_users,
waiting_requests=waiting_requests)
def recycle_trash(mail_id, mail_identity, outbox_status):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
if mail_identity:
recycle_mail_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;"""
sqlite_connection.execute(recycle_mail_query, (0, mail_id))
else:
if outbox_status == True:
recycle_mail_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;"""
sqlite_connection.execute(recycle_mail_query, (0, mail_id))
else:
select_mail_date = """SELECT mail_date FROM mail WHERE mail_id = ?;"""
db_mail_date = sqlite_connection.execute(
select_mail_date, (mail_id,))
mail_date = 0
for date in db_mail_date:
mail_date = date[0]
break
recycle_mails_query = """UPDATE mail SET sender_trashed = ? WHERE mail_date = ?;"""
sqlite_connection.execute(recycle_mails_query, (0, mail_date))
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for("user_mail_bp.trash"))
def view_profile():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
user_email = session['email']
sqlite_connection = sqlite3.connect('Mail_DB.db')
select_user_query = """SELECT user_id, user_name, user_email, user_gender
,user_date_of_birth, user_city, user_country, user_contact
, user_account_creation_date FROM user
WHERE user_email = ?;"""
select_user_query_data = (user_email,)
user_data = sqlite_connection.execute(
select_user_query, select_user_query_data)
user_record = {}
for data in user_data:
user_record['ID'] = data[0]
user_record['NAME'] = data[1]
user_record['EMAIL'] = data[2]
user_record['GENDER'] = data[3]
user_record['DATE_OF_BIRTH'] = data[4]
user_record['CITY'] = data[5]
user_record['COUNTRY'] = data[6]
user_record['CONTACT'] = data[7]
user_record['ACCOUNT_CREATION_DATE'] = data[8]
sqlite_connection.close()
return render_template("User_Profile/ViewProfile.html", user=user_record, user_mail=session["email"])
def view_mail(mail_id):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('MAIL_DB.db')
select_mail_query = """SELECT sender_id, mail_subject, mail_body, mail_date, mail_id FROM mail WHERE mail_id = ?;"""
mail_record = sqlite_connection.execute(select_mail_query, (mail_id,))
mail_data = {}
for data in mail_record:
mail_data['sender_mail'] = get_user_email(data[0])
mail_data['mail_subject'] = data[1]
mail_data['mail_body'] = data[2]
mail_data['mail_date'] = remove_milli_seconds(data[3])
mail_data['mail_id'] = data[4]
if check_attachment_exist(mail_id):
select_attachment_query = """SELECT attachment_file FROM attachment WHERE mail_id = ?;"""
attach_record = sqlite_connection.execute(
select_attachment_query, (mail_id,))
attachments = []
for file in attach_record:
attachments.append(file[0])
mail_data['attachments'] = attachments
update_query = """UPDATE mail SET mail_seen = ? WHERE mail_id = ?;"""
update_query_data = (1, mail_id)
sqlite_connection.execute(update_query, update_query_data)
sqlite_connection.commit()
sqlite_connection.close()
return render_template('User_Mail/view_mail.html', mail=mail_data, user_mail=session["email"])
def view_file(file_name):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
try:
return send_from_directory(current_app.config['USER_ATTACHMENTS'], filename=secure_filename(file_name), as_attachment=False)
except FileNotFoundError:
abort(404)
def see_all_mail():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
inbox = return_inbox(get_user_id(session["email"]))
outbox = return_outbox(get_user_id(session["email"]))
all_mail = sort_mails(inbox + outbox)
return render_template("User_Mail/view_all_mail.html", all_mail=all_mail, user_email=session["email"])
def send_multi_users():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
allReceiversMails = json.loads(request.form["allMail"])
if(len(allReceiversMails) == 1):
return redirect(url_for("user_mail_bp.compose_to_email", user_mail=allReceiversMails[0]))
return render_template("User_Mail/composeEmail.html", userName=session['email'], to=allReceiversMails, max_length=current_app.config["MAX_CONTENT_LENGTH"])
def remove_request(request_id):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
delete_query = "DELETE FROM waiting_list WHERE waiting_id = ?;"
sqlite_connection.execute(delete_query, request_id)
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for('admin_bp.waiting_requests'))
def view_all_users():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('Mail_DB.db')
select_allusers_query = """SELECT user_id , user_name, user_email FROM user WHERE user_id != ?;"""
allUsers = sqlite_connection.execute(
select_allusers_query, (get_user_id(session["email"]),)).fetchall()
sqlite_connection.close()
return render_template("User_Profile/allUsers.html", allUsers=allUsers, user_mail=session["email"])
def inbox_to_trash(mail_id):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
update_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;"""
sqlite_connection.execute(update_query, (1, mail_id))
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for("user_mail_bp.see_inbox"))
def user_activity():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
user_id = get_user_id(session["email"])
sqlite_connection = sqlite3.connect("MAIL_DB.db")
update_active_query = """UPDATE user SET user_active = ? WHERE user_id = ?;"""
sqlite_connection.execute(update_active_query, (1, user_id))
sqlite_connection.commit()
sqlite_connection.close()
return jsonify({"active": True})
def admin_authorize(user_mail):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
remove_mail_query = """DELETE FROM unauthorize WHERE user_email = ?;"""
sqlite_connection.execute(remove_mail_query, (user_mail, ))
sqlite_connection.commit()
sqlite_connection.close()
flash("authorized successfully", "authorize")
return redirect(url_for("admin_bp.admin_page"))
def admin_unauthorize(user_mail):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
insert_mail_query = """INSERT INTO unauthorize (user_email) VALUES(?);"""
sqlite_connection.execute(insert_mail_query, (user_mail, ))
sqlite_connection.commit()
sqlite_connection.close()
flash("unauthorized successfully", "unauthorize")
return redirect(url_for("admin_bp.admin_page"))
def logout():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('Mail_DB.db')
user_update_query = """UPDATE user SET user_active = ? WHERE user_email = ?;"""
user_update_query_data = (0, session["email"])
sqlite_connection.execute(user_update_query, user_update_query_data)
sqlite_connection.commit()
sqlite_connection.close()
session.pop('email', None)
return redirect(url_for("forms_bp.login_form_page"))
def get_mail_body(mail_id):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_mail_query = "SELECT mail_body FROM mail WHERE mail_id = ?;"
db_output = sqlite_connection.execute(select_mail_query, (mail_id,))
mail_body = ""
for mail in db_output:
mail_body = mail[0]
sqlite_connection.close()
return jsonify(mail_body)
def unseen_inbox(user_mail):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_unseen_mail = """SELECT mail_id FROM mail WHERE receiver_id = ? AND mail_seen = ? AND receiver_trashed = ?;"""
values = (get_user_id(user_mail), 0, 0)
db_unseen_mails = sqlite_connection.execute(select_unseen_mail, values)
counter = 0
for mail in db_unseen_mails:
counter += 1
sqlite_connection.close()
return jsonify({"unseen_mails": counter})
def see_inbox():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
inbox = return_inbox(get_user_id(session["email"]))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_name = "SELECT user_name FROM user WHERE user_email = ?;"
db_user_name = sqlite_connection.execute(select_name, (session["email"],))
user_name = ""
for user in db_user_name:
user_name = user[0]
sqlite_connection.close()
return render_template("User_Mail/inbox.html", inbox_mails=inbox, user_mail=session["email"], user_name=user_name)
def delete_account():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('Mail_DB.db')
user_delete_query = """DELETE FROM user WHERE user_email = ? AND user_password = ?;"""
sqlite_connection.execute(
user_delete_query, (session['email'], session['password']))
sqlite_connection.commit()
sqlite_connection.close()
session.pop('email', None)
session.pop('password', None)
return "<h1>Your Account is Deleted successfully</h1>"
def contact_us():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_query = """SELECT admin_email, admin_phone FROM admin ORDER BY admin_id ASC;"""
db_admins = sqlite_connection.execute(select_query)
all_admins = []
admin_data = {}
for admin in db_admins:
admin_data["admin_email"] = admin[0]
admin_data["admin_phone"] = admin[1]
all_admins.append(admin_data)
admin_data = {}
return render_template("User_Profile/contactUs.html", all_admins=all_admins, user_mail=session["email"])
def file_extensions_validation():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
file_extensions = json.loads(request.args.get("fileExtensions"))
all_files = []
file_data = {}
for file in file_extensions:
file_data["file_name"] = file
if validate_file_extension(file) == False:
file_data["valid"] = False
else:
file_data["valid"] = True
all_files.append(file_data)
file_data = {}
return jsonify(all_files)
def all_mail_to_trash(mail_id, trash_identity):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('MAIL_DB.db')
update_query = ""
if trash_identity == 1:
update_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;"""
else:
update_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;"""
sqlite_connection.execute(update_query, (1, mail_id))
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for("user_mail_bp.see_all_mail"))
def sending_email():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sender_mail = session['email']
receiver_mail = request.form['recievers']
all_receivers = receiver_mail.split(" ")
mail_subject = request.form['subject']
mail_body = request.form['mailInfo'].strip()
mail_date = datetime.now()
sender_id = get_user_id(session['email'])
sqlite_connection = sqlite3.connect('MAIL_DB.db')
insert_mail_query = """INSERT INTO mail (sender_id, receiver_id, mail_subject, mail_body, mail_date, multiple_receivers)
VALUES(?, ?, ?, ?, ?, ?);"""
multiple_receivers = 1
if len(all_receivers) == 1:
multiple_receivers = 0
for receiver in all_receivers:
insert_mail_query_data = (sender_id, get_user_id(
receiver), mail_subject, mail_body, mail_date, multiple_receivers)
sqlite_connection.execute(insert_mail_query, insert_mail_query_data)
uploaded_files = request.files.getlist("user_files")
if check_files_numbers(uploaded_files):
if check_file_extension(uploaded_files) == False:
flash('Not Allowed Extension', "danger")
return redirect(url_for('user_mail_bp.compose_email'))
os.makedirs(current_app.config['USER_ATTACHMENTS'], exist_ok=True)
for user_file in uploaded_files:
file_name = secure_filename(user_file.filename)
user_file.save(os.path.join(
current_app.config['USER_ATTACHMENTS'], file_name))
save_attachment_query = """INSERT INTO attachment(mail_id, attachment_file)
VALUES(?, ?);"""
sqlite_select_query = """SELECT mail_id FROM mail WHERE sender_id = ? AND mail_date = ?;"""
sqlite_select_query_data = (sender_id, mail_date)
mail_ids = sqlite_connection.execute(
sqlite_select_query, sqlite_select_query_data)
for mail_id in mail_ids:
sqlite_connection.execute(
save_attachment_query, (mail_id[0], user_file.filename))
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for('user_mail_bp.compose_email'))
def email_validator(input_mail):
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
if "@" not in input_mail:
input_mail = input_mail + "@"
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_all_mails_query = """SELECT admin_email FROM admin;"""
db_all_mails = sqlite_connection.execute(select_all_mails_query)
for mail in db_all_mails:
if input_mail.split("@")[0] == mail[0].split("@")[0]:
response_message = {
"message": "This mail is taken",
"valid": False
}
return jsonify(response_message)
response_message = {"message": "valid mail name", "valid": True}
return jsonify(response_message)
def edit_profile():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
new_name = request.form['user_name']
new_dateOfBirth = request.form['user_birthDate']
new_city = request.form['user_city']
new_country = request.form['user_country']
sqlite_connection = sqlite3.connect('Mail_DB.db')
user_update_query = """UPDATE user SET user_name = ?, user_date_of_birth = ?,
user_city = ?, user_country = ? WHERE user_email = ?;"""
user_update_query_data = (
new_name, new_dateOfBirth, new_city, new_country, session['email'])
sqlite_connection.execute(user_update_query, user_update_query_data)
sqlite_connection.commit()
sqlite_connection.close()
flash("Successfully!!!", "success")
return redirect(url_for("user_profile_bp.edit_profile_form"))
def edit_profile_form():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect('Mail_DB.db')
select_user_query = """SELECT * FROM user WHERE user_email = ?;"""
select_user_query_data = (session['email'],)
user_data = sqlite_connection.execute(
select_user_query, select_user_query_data)
user_record = {}
for data in user_data:
user_record['NAME'] = data[1]
user_record['EMAIL'] = data[2]
user_record['DATE_OF_BIRTH'] = data[5]
user_record['CITY'] = data[6]
user_record['COUNTRY'] = data[7]
user_record['CONTACT'] = data[8]
user_record['ACCOUNT_CREATRION_DATE'] = data[9]
sqlite_connection.close()
return render_template('User_Profile/editProfileForm.html', user=user_record, user_mail=session["email"])
def add_admin():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
admin_mail = request.form.get("adminMail")
admin_password = request.form.get("adminPassword")
admin_phone = request.form.get("adminPhone")
admin_password = bcrypt.generate_password_hash(admin_password).decode(
"UTF-8")
sqlite_connection = sqlite3.connect("MAIL_DB.db")
add_admin_query = """INSERT INTO admin (admin_email, admin_password, admin_phone) VALUES(?, ?, ?);"""
sqlite_connection.execute(add_admin_query,
(admin_mail, admin_password, admin_phone))
add_user_query = """INSERT INTO user (user_name, user_email, user_password,
user_gender, user_date_of_birth, user_contact, user_account_creation_date, user_city, user_country)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);"""
add_user_values = ("admin", admin_mail, admin_password, "other", "none",
admin_phone, datetime.now(), "none", "none")
sqlite_connection.execute(add_user_query, add_user_values)
sqlite_connection.commit()
sqlite_connection.close()
return redirect(url_for("admin_bp.admin_page"))
def waiting_requests():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
select_requests_query = """SELECT * FROM waiting_list;"""
db_waiting_requests = sqlite_connection.execute(select_requests_query)
all_requests = []
request_data = {}
for request in db_waiting_requests:
request_data["user_id"] = request[0]
request_data["user_name"] = request[1]
request_data["user_email"] = request[2]
request_data["user_gender"] = request[4]
request_data["user_date_of_birth"] = request[5]
request_data["user_city"] = request[6]
request_data["user_country"] = request[7]
request_data["user_contact"] = request[8]
all_requests.append(request_data)
request_data = {}
sqlite_connection.close()
return render_template("admin_templates/waitingRequests.html",
all_requests=all_requests)
def trash():
if check_authentication() == False:
return redirect(url_for('forms_bp.login_form_page'))
sqlite_connection = sqlite3.connect("MAIL_DB.db")
user_id = get_user_id(session["email"])
select_inbox_trash = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail WHERE
receiver_id = ? AND receiver_trashed = ?;"""
db_inbox_trash = sqlite_connection.execute(
select_inbox_trash, (user_id, 1))
mail_data = {}
inbox_trash = []
for mail in db_inbox_trash:
mail_data["mail_id"] = mail[0]
mail_data["sender_mail"] = get_user_email(mail[1])
mail_data["mail_subject"] = mail[2]
mail_data["mail_date"] = remove_milli_seconds(mail[3])
mail_data["mail_identity"] = True
mail_data["outbox_status"] = False
inbox_trash.append(mail_data)
mail_data = {}
select_outbox_single_trash = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail WHERE
sender_id = ? AND sender_trashed = ? AND multiple_receivers = ?;"""
db_outbox_single_trash = sqlite_connection.execute(
select_outbox_single_trash, (user_id, 1, 0))
outbox_single_trash = []
for mail in db_outbox_single_trash:
mail_data["mail_id"] = mail[0]
mail_data["sender_mail"] = get_user_email(mail[1])
mail_data["mail_subject"] = mail[2]
mail_data["mail_date"] = remove_milli_seconds(mail[3])
mail_data["mail_identity"] = False
mail_data["outbox_status"] = True
outbox_single_trash.append(mail_data)
mail_data = {}
select_distinct_mail_dates = """SELECT DISTINCT mail_date FROM mail WHERE sender_id = ? AND sender_trashed = ?
AND multiple_receivers = ?;"""
db_distinct_dates = sqlite_connection.execute(
select_distinct_mail_dates, (user_id, 1, 1))
outbox_multiple_trash = []
for mail_date in db_distinct_dates:
select_multiple_trash_mail_query = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail
WHERE sender_id = ? AND sender_trashed = ? AND multiple_receivers = ?
AND mail_date = ?;"""
db_multiple_trash_mail = sqlite_connection.execute(
select_multiple_trash_mail_query, (user_id, 1, 1, mail_date[0]))
for mail in db_multiple_trash_mail:
mail_data["mail_id"] = mail[0]
mail_data["sender_mail"] = "Multiple Receivers"
mail_data["mail_subject"] = mail[2]
mail_data["mail_date"] = remove_milli_seconds(mail[3])
mail_data["mail_identity"] = False
mail_data["outbox_status"] = True + True
outbox_multiple_trash.append(mail_data)
mail_data = {}
break
all_mail = sort_mails_by_date(
inbox_trash + outbox_single_trash + outbox_multiple_trash)
sqlite_connection.close()
return render_template("User_Mail/view_trash.html", all_mail=all_mail, user_mail=session["email"])
